[SQL] using calculated column in where-clause
Hi! I'd like to do some calculation with values from the table, show them a new column and use the values in a where-clause. Something like this select a, b , a*b as c from ta where c=2; But postgresql complains, that column "c" does not exist. Do I have to repeat the calculation (which might be even more complex :-) ) in the "where"-clause, or is there a better way? Thanks in advance. Best regards Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using calculated column in where-clause
Patrick Scharrenberg <[EMAIL PROTECTED]> schrieb: > Something like this > select a, b , a*b as c from ta where c=2; > > But postgresql complains, that column "c" does not exist. > > Do I have to repeat the calculation (which might be even more complex yes. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using calculated column in where-clause
On Tue, Jun 17, 2008 at 2:46 PM, Patrick Scharrenberg <[EMAIL PROTECTED]> wrote: > Hi! > > I'd like to do some calculation with values from the table, show them a > new column and use the values in a where-clause. > > Something like this > select a, b , a*b as c from ta where c=2; > > But postgresql complains, that column "c" does not exist. > > Do I have to repeat the calculation (which might be even more complex > :-) ) in the "where"-clause, or is there a better way? Pretty much yes. Trying to do tricks using subselects may result in substandard performing query plans. You can always do something like: select * from (select a,b,a*b as c) as z where z.c = 2 but if it's slower don't blame me. The nice thing here is that you can index on that function, which is the real issue with performance, since otherwise you'll likely see a sequential scan every time. create index ta_atimesb on ta ((a*b)); and from then on the query should run pretty fast. That's really more important than if you have to put it twice on the same query line. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using calculated column in where-clause
Andreas Kretschmer wrote: >> Do I have to repeat the calculation (which might be even more complex > yes. Short and pregnant! :-) Thanks! Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] order by when using cursors
Hi! I did some experiments with cursors and found that my data doesn't get sorted by the "order by"-statement. Here is what I did: CREATE TABLE ta ( a integer NOT NULL, b integer NOT NULL ); insert into ta values(3,1); insert into ta values(1,2); insert into ta values(4,3); insert into ta values(2,4); CREATE OR REPLACE FUNCTION testcur( OUT a integer, OUT b integer ) RETURNS SETOF RECORD AS $$ DECLARE cur refcursor; BEGIN OPEN cur FOR SELECT * FROM ta ORDER BY a DESC; LOOP FETCH cur INTO a,b; IF not found THEN exit; ELSE RETURN NEXT; END IF; END LOOP; CLOSE cur; END; $$ LANGUAGE 'PLPGSQL' ; SELECT * FROM testcur(); As the result I get: 3 1 1 2 4 3 2 4 Which is not ordered by column a!? Is this intended? Am I doing something wrong? I'm using Postgresql 8.3.1 Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order by when using cursors
Hello it's known problem - column and variable names collision, so when you use any SQL statement inside procedure you have to be carefully about using variable names. postgres=# CREATE OR REPLACE FUNCTION testcur( OUT _a integer, OUT _b integer ) RETURNS SETOF RECORD AS $$ DECLARE cur refcursor; BEGIN OPEN cur FOR SELECT * FROM ta ORDER BY a DESC; LOOP FETCH cur INTO _a, _b; IF not found THEN exit; ELSE RETURN NEXT; END IF; END LOOP; CLOSE cur; END; $$ LANGUAGE 'PLPGSQL' ; postgres=# select *from testcur(); _a | _b + 4 | 3 3 | 1 2 | 4 1 | 2 (4 rows) postgres=# Regards Pavel Stehule 2008/6/18 Patrick Scharrenberg <[EMAIL PROTECTED]>: > Hi! > > I did some experiments with cursors and found that my data doesn't get > sorted by the "order by"-statement. > > Here is what I did: > > > > CREATE TABLE ta ( > a integer NOT NULL, > b integer NOT NULL > ); > > insert into ta values(3,1); > insert into ta values(1,2); > insert into ta values(4,3); > insert into ta values(2,4); > > CREATE OR REPLACE FUNCTION testcur( OUT a integer, OUT b integer ) > RETURNS SETOF RECORD AS $$ > DECLARE >cur refcursor; > BEGIN >OPEN cur FOR SELECT * FROM ta ORDER BY a DESC; >LOOP >FETCH cur INTO a,b; >IF not found THEN >exit; >ELSE >RETURN NEXT; >END IF; >END LOOP; >CLOSE cur; > END; > $$ LANGUAGE 'PLPGSQL' ; > > SELECT * FROM testcur(); > > > > As the result I get: > > 3 1 > 1 2 > 4 3 > 2 4 > > > Which is not ordered by column a!? > > Is this intended? > Am I doing something wrong? > > I'm using Postgresql 8.3.1 > > Patrick > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order by when using cursors
2008/6/18 Pavel Stehule <[EMAIL PROTECTED]>: > Hello > > it's known problem - column and variable names collision, so when you > use any SQL statement inside procedure you have to be carefully about > using variable names. > > postgres=# CREATE OR REPLACE FUNCTION testcur( OUT _a integer, OUT _b integer > ) > RETURNS SETOF RECORD AS $$ > DECLARE > cur refcursor; > BEGIN > OPEN cur FOR SELECT * FROM ta ORDER BY a DESC; > LOOP > FETCH cur INTO _a, _b; > IF not found THEN > exit; > ELSE > RETURN NEXT; > END IF; > END LOOP; > CLOSE cur; > END; > $$ LANGUAGE 'PLPGSQL' ; > one note: when you unlike prefixes in result, you can use in ORDER BY expression ordinal number of an output column, in this case postgres=# CREATE OR REPLACE FUNCTION testcur( OUT a integer, OUT b integer ) RETURNS SETOF RECORD AS $$ DECLARE cur refcursor; BEGIN OPEN cur FOR SELECT * FROM ta ORDER BY 1 DESC; LOOP FETCH cur INTO a, b; IF not found THEN exit; ELSE RETURN NEXT; END IF; END LOOP; CLOSE cur; END; $$ LANGUAGE 'PLPGSQL' ; other solution is using qualified names everywhere: CREATE OR REPLACE FUNCTION testcur( OUT a integer, OUT b integer ) RETURNS SETOF RECORD AS $$ DECLARE cur refcursor; BEGIN OPEN cur FOR SELECT ta.a, ta.b FROM ta ORDER BY ta.a DESC; -- ta.a qualified name LOOP FETCH cur INTO a, b; IF not found THEN exit; ELSE RETURN NEXT; END IF; END LOOP; CLOSE cur; END; $$ LANGUAGE 'PLPGSQL' ; Pavel > > postgres=# select *from testcur(); > _a | _b > + > 4 | 3 > 3 | 1 > 2 | 4 > 1 | 2 > (4 rows) > > postgres=# > > Regards > Pavel Stehule > > > 2008/6/18 Patrick Scharrenberg <[EMAIL PROTECTED]>: >> Hi! >> >> I did some experiments with cursors and found that my data doesn't get >> sorted by the "order by"-statement. >> >> Here is what I did: >> >> >> >> CREATE TABLE ta ( >> a integer NOT NULL, >> b integer NOT NULL >> ); >> >> insert into ta values(3,1); >> insert into ta values(1,2); >> insert into ta values(4,3); >> insert into ta values(2,4); >> >> CREATE OR REPLACE FUNCTION testcur( OUT a integer, OUT b integer ) >> RETURNS SETOF RECORD AS $$ >> DECLARE >>cur refcursor; >> BEGIN >>OPEN cur FOR SELECT * FROM ta ORDER BY a DESC; >>LOOP >>FETCH cur INTO a,b; >>IF not found THEN >>exit; >>ELSE >>RETURN NEXT; >>END IF; >>END LOOP; >>CLOSE cur; >> END; >> $$ LANGUAGE 'PLPGSQL' ; >> >> SELECT * FROM testcur(); >> >> >> >> As the result I get: >> >> 3 1 >> 1 2 >> 4 3 >> 2 4 >> >> >> Which is not ordered by column a!? >> >> Is this intended? >> Am I doing something wrong? >> >> I'm using Postgresql 8.3.1 >> >> Patrick >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
