Re: [GENERAL] Hash Support Function
>I'm developing a new type, and want to have hash index on it. >I must write a hash function for the new type, according to the PG manual, >section 35.14.3. >However, there is no example for this function. Actually signature of the hash >support function >would be sufficient for me. Hello, Have a look at pghashlib: https://github.com/markokr/pghashlib regards, Marc Mamin
Re: [GENERAL] Stored Procedure table/column args
Hello, Create a view as described @ http://www.postgresql.org/message-id/flat/3c055b7e.bb52f...@but.auc.dk#3c055b7e.bb52f...@but.auc.dk create view my_tbldescription as select u.usename, t.typname AS tblname, a.attname, a.atttypid, n.typname AS atttypname, int4larger(a.attlen, a.atttypmod - 4) AS atttyplen, a.attnotnull, a.attnum from pg_user u, pg_type t, pg_attribute a, pg_type n where u.usesysid = t.typowner and t.typrelid = a.attrelid and t.typtype = 'c' and not (t.typname ~* 'pg_') and n.typelem = a.atttypid and substr(n.typname, 1, 1) = '_' and a.attnum > 0 ; And then create functions using that view. create or replace function table_exists (tbl varchar) returns boolean AS $$ DECLARE x integer; BEGIN Execute 'select count(*) from my_tbldescription where tblname=$1' into x using tbl; if (x>0) then RETURN TRUE; else RETURN FALSE; end if; END; $$ LANGUAGE plpgsql; create or replace function column_exists (col varchar) returns boolean AS $$ DECLARE x integer; BEGIN Execute 'select count(*) from my_tbldescription where attname=$1' into x using col; if (x>0) then RETURN TRUE; else RETURN FALSE; end if; END; Regards Sameer
Re: [GENERAL] Call for design: PostgreSQL mugs
On 09/04/2013 12:08 AM, Andreas 'ads' Scherbaum wrote: PostgreSQL folks! We are looking for the next big thing. Actually, it's a bit smaller: a new design for mugs. So far we had big blue elephants, small blue elephants, frosty elephants, white SQL code on black mugs ... Now it's time to design something new. What's in for you? Fame, of course - we will announce the designer of the next mug along with the mugs itself. Plus 4 mugs for you and your friends. Do you have a cool idea? Please let us know. Either reply here or send an email to pgeu-bo...@postgresql.org. So, we discussed a number ideas here on the lists, mainly a new text for the mugs. But it does not look like we have a winner. There was also this cheap shot at MySQL, but I think we all agree that we don't need this kind of design. This raises the question: do we want 1) a design with a new text on it (some ideas were discussed here) 2) a design with a new graphic Discuss please. -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgeu-general] Call for design: PostgreSQL mugs
On Sun, September 8, 2013 15:27, Andreas 'ads' Scherbaum wrote: > > So, we discussed a number ideas here on the lists, mainly a new text for > the mugs. But it does not look like we have a winner. > > There was also this cheap shot at MySQL, but I think we all agree that > we don't need this kind of design. > Clearly we did *not* all agree.. :-) But here is another old one that I always liked, and would like to see processed onto a mug. It would need some work, but the idea seems excellent: a large, impressive elephant head, frontal, with the text: "Never Forgets" or "PostgreSQL Never Forgets" http://www.gsbrown.org/compuserve/all-new-electronic-mail-1982-04/elephant-ad-800.jpg ( Greg S. sent me an .xcf file (2MB) which I could forward if anyone is interested to improving it... ) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Call for design: PostgreSQL mugs
On 09/09/13 01:27, Andreas 'ads' Scherbaum wrote: On 09/04/2013 12:08 AM, Andreas 'ads' Scherbaum wrote: PostgreSQL folks! We are looking for the next big thing. Actually, it's a bit smaller: a new design for mugs. So far we had big blue elephants, small blue elephants, frosty elephants, white SQL code on black mugs ... Now it's time to design something new. What's in for you? Fame, of course - we will announce the designer of the next mug along with the mugs itself. Plus 4 mugs for you and your friends. Do you have a cool idea? Please let us know. Either reply here or send an email to pgeu-bo...@postgresql.org. So, we discussed a number ideas here on the lists, mainly a new text for the mugs. But it does not look like we have a winner. There was also this cheap shot at MySQL, but I think we all agree that we don't need this kind of design. This raises the question: do we want 1) a design with a new text on it (some ideas were discussed here) 2) a design with a new graphic Discuss please. How about an elephant on the outside and a matching reverse image inside - with the slogan something like 'PostgreSQL solid throughout'. Cheers, Gavin
Re: [GENERAL] Call for design: PostgreSQL mugs
PostgreSQL - (the worlds database) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sum of columns
Hi, this is my query: SELECT user, sum(CASE WHEN lev >= 50 AND lev < 70 THEN 1 ELSE 0 END) as a, sum(CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) as b, sum(CASE WHEN lev >= 80 AND lev <= 90 THEN 1 ELSE 0 END) as c, sum(CASE WHEN lev > 90 THEN 1 ELSE 0 END) as d, (SELECT a + b + a + d) AS matches FROM t_temp_fts GROUP BY user' I like to add up the 4 columns a,b,c and d of every user, but it doesn't work like this. Does anyone know a solution Janek Sendrowski
Re: [GENERAL] Sum of columns
does sum (case when lev >= 50 then 1 else 0 end) as matches do what you want? On Sun, Sep 8, 2013 at 9:12 PM, wrote: > Hi, > > this is my query: > SELECT user, > sum(CASE WHEN lev >= 50 AND lev < 70 THEN 1 ELSE 0 END) as a, > sum(CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) as b, > sum(CASE WHEN lev >= 80 AND lev <= 90 THEN 1 ELSE 0 END) as c, > sum(CASE WHEN lev > 90 THEN 1 ELSE 0 END) as d, > (SELECT a + b + a + d) AS matches > FROM t_temp_fts > GROUP BY user' > > I like to add up the 4 columns a,b,c and d of every user, but it doesn't > work like this. > Does anyone know a solution > > Janek Sendrowski > -- The person who says it cannot be done should not interrupt the person who is doing it. -- Chinese Proverb
Re: [GENERAL] Sum of columns
On 9 Září 2013, 3:12, jane...@web.de wrote: > Hi, this is my query: SELECT user, sum(CASE WHEN lev >= 50 > AND lev < 70 THEN 1 ELSE 0 END) as a, > sum(CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) as b, > sum(CASE WHEN lev >= 80 AND lev 90 THEN 1 ELSE 0 END) as d, > (SELECT a + b + a + d) AS matches > FROM t_temp_fts > GROUP BY user' I like to add up the 4 columns a,b,c and d > of every user, but it doesn't work like this. Does anyone > know a solution Janek Sendrowski Hi, it doesn't work like that - the inner select makes no sense for various reasons. I'd bet what you want is something this: SELECT user, a, b, d, (a + b + d) AS matches FROM ( SELECT user, sum(CASE WHEN lev >= 50 AND lev < 70 THEN 1 ELSE 0 END) as a, sum(CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) as b, sum(CASE WHEN lev >= 80 AND lev < 90 THEN 1 ELSE 0 END) as d, FROM t_temp_fts GROUP BY user ) foo i.e. it takes the t_temp_fts table, computes the partial results and then passes the results to the outer query to evaluate the addition. There's an alternative doing all of that in a single query: SELECT user, a, b, d, (a + b + d) AS matches FROM ( SELECT user, sum(CASE WHEN lev >= 50 AND lev < 70 THEN 1 ELSE 0 END) as a, sum(CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) as b, sum(CASE WHEN lev >= 80 AND lev < 90 THEN 1 ELSE 0 END) as d, sum(CASE WHEN lev >= 50 AND lev < 70 THEN 1 ELSE 0 END) + sum(CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) + sum(CASE WHEN lev >= 80 AND lev < 90 THEN 1 ELSE 0 END) as matches, FROM t_temp_fts GROUP BY user ) foo or you could add directly the CASE statements like this: SELECT user, a, b, d, (a + b + d) AS matches FROM ( SELECT user, sum(CASE WHEN lev >= 50 AND lev < 70 THEN 1 ELSE 0 END) as a, sum(CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) as b, sum(CASE WHEN lev >= 80 AND lev < 90 THEN 1 ELSE 0 END) as d, sum((CASE WHEN lev >= 50 AND lev < 70 THEN 1 ELSE 0 END) + (CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) + (CASE WHEN lev >= 80 AND lev < 90 THEN 1 ELSE 0 END)) as matches, FROM t_temp_fts GROUP BY user ) foo All of this should return return the same results. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Sum of columns
On 09/08/2013 07:12 PM, jane...@web.de wrote:
Hi,
this is my query:
SELECT user,
sum(CASE WHEN lev >= 50 AND lev < 70 THEN 1 ELSE 0 END) as a,
sum(CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) as b,
sum(CASE WHEN lev >= 80 AND lev <= 90 THEN 1 ELSE 0 END) as c,
sum(CASE WHEN lev > 90 THEN 1 ELSE 0 END) as d,
(SELECT a + b + a + d) AS matches
FROM t_temp_fts
GROUP BY user'
I like to add up the 4 columns a,b,c and d of every user, but it
doesn't work like this.
Does anyone know a solution
Janek Sendrowski
How far does this get you?
insert into t_temp_fts values('rob', 51), ('rob', 71), ('rob', 81),
('rob', 91);
insert into t_temp_fts values('jon', 51), ('jon', 71), ('jon', 81),
('jon', 91);
SELECT distinct usern,
(select count(*) from t_temp_fts i where o.usern = i.usern and
lev >= 50 AND lev < 70) as a,
(select count(*) from t_temp_fts i where o.usern = i.usern and
lev >= 70 AND lev < 80)as b ,
(select count(*) from t_temp_fts i where o.usern = i.usern and
lev >= 80 AND lev < 90)as c ,
(select count(*) from t_temp_fts i where o.usern = i.usern and
lev > 90) as d
from t_temp_fts o
;
usern | a | b | c | d
---+---+---+---+---
jon | 1 | 1 | 1 | 1
rob | 1 | 1 | 1 | 1
(2 rows)
Re: [GENERAL] SQL Path in psql
On Fri, Sep 6, 2013 at 1:58 PM, David Kerr wrote:
> I suspect this feature makes more sense on a windows platform. On linux
> where we can
> go psql -f ${SQLPATH}/file.sql . it becomes less pressing. Even Oracle
> on unix/linux where you can go sqlplus < makes
> it less a requirement.
>
I don't see why you couldn't do the same thing in command prompt on Windows:
SET PGSQLPATH=C:\somedir
psql -f %PGSQLPATH%\file.sql
Naturally, you could configure a permanent environment variable instead of
using SET.
Re: [GENERAL] [pgeu-general] Call for design: PostgreSQL mugs
Hello all, "Elephants Never Forget" sounds like a good idea. It refers to reliability of the database (from a transaction perspective) and ability to recover "lost data". http://www.scientificamerican.com/article.cfm?id=elephants-never-forget Regards, Jayadevan On Sun, Sep 8, 2013 at 9:20 PM, Erik Rijkers wrote: > On Sun, September 8, 2013 15:27, Andreas 'ads' Scherbaum wrote: > > > > So, we discussed a number ideas here on the lists, mainly a new text for > > the mugs. But it does not look like we have a winner. > > > > There was also this cheap shot at MySQL, but I think we all agree that > > we don't need this kind of design. > > > > Clearly we did *not* all agree.. :-) > > But here is another old one that I always liked, and would like to see > processed onto a mug. It would need some work, but > the idea seems excellent: > a large, impressive elephant head, frontal, with the text: "Never Forgets" > or "PostgreSQL Never Forgets" > > > http://www.gsbrown.org/compuserve/all-new-electronic-mail-1982-04/elephant-ad-800.jpg > > ( Greg S. sent me an .xcf file (2MB) which I could forward if anyone is > interested to improving it... ) > > > > > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
