Re: [GENERAL] Let-bindings in SQL statements
On Sat, Feb 11, 2012 at 12:42, Jasen Betts wrote: > There is no need. now() is tagged as stable. it will only be executed once. > the planner will figure this out for you. Actually that's not always true. In index condition arguments, the expression would indeed be executed just once. But in filter clauses (e.g. seq scan), the whole expression is executed once per row, which is a bit inefficient. Of course this makes no visible difference for now(), since it always returns the same value -- the transaction start time. (I submitted a patch to improve this, but it's not certain whether it will be included in PostgreSQL 9.2 or not) Regards, Marti -- 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] Let-bindings in SQL statements
On 2012-01-26, David W Noon wrote: > > Try using CURRENT_TIMESTAMP instead. In fact, CURRENT_TIMESTAMP is > more traditional SQL than now(). I don't have an ANSI standard handy, > so I cannot be certain when now() was added, if ever; but I have been > using CURRENT TIMESTAMP (space instead of vinculum) under DB2 for 20 > years or more. The planner will rewrite CURRENT_TIMESTAMP to now() :) -- ⚂⚃ 100% natural -- 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] Let-bindings in SQL statements
On 2012-01-26, David Johnston wrote: > Is it possible to do the equivalent of let-bindings in a pure SQL function? > I have a SELECT that invokes "now" multiple times. It would be nicer to do > it only once and reuse the value. Something like this: There is no need. now() is tagged as stable. it will only be executed once. the planner will figure this out for you. -- ⚂⚃ 100% natural -- 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] Let-bindings in SQL statements
On Thu, Jan 26, 2012 at 3:37 PM, Jon Smark wrote: > Hi, > > Is it possible to do the equivalent of let-bindings in a pure SQL function? > I have a SELECT that invokes "now" multiple times. It would be nicer > to do it only once and reuse the value. Something like this: > > LET right_now = SELECT now () IN > SELECT * FROM my_table WHERE right_now >= start AND ... > > In PL/pgSQL this is easy, but I wonder about SQL... > > WITH param AS ( select now() as p_start, somefunc() as p_something ) SELECT * FROM param,my_table WHERE right_now >= param.p_start AND ...
Re: [GENERAL] Let-bindings in SQL statements
On Thu, 26 Jan 2012 06:37:49 -0800 (PST), Jon Smark wrote about [GENERAL] Let-bindings in SQL statements: >Is it possible to do the equivalent of let-bindings in a pure SQL >function? I have a SELECT that invokes "now" multiple times. It would >be nicer to do it only once and reuse the value. Something like this: > >LET right_now = SELECT now () IN >SELECT * FROM my_table WHERE right_now >= start AND ... > >In PL/pgSQL this is easy, but I wonder about SQL... Try using CURRENT_TIMESTAMP instead. In fact, CURRENT_TIMESTAMP is more traditional SQL than now(). I don't have an ANSI standard handy, so I cannot be certain when now() was added, if ever; but I have been using CURRENT TIMESTAMP (space instead of vinculum) under DB2 for 20 years or more. -- Regards, Dave [RLU #314465] *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* dwn...@ntlworld.com (David W Noon) *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* signature.asc Description: PGP signature
Re: [GENERAL] Let-bindings in SQL statements
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jon Smark Sent: Thursday, January 26, 2012 9:38 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Let-bindings in SQL statements Hi, Is it possible to do the equivalent of let-bindings in a pure SQL function? I have a SELECT that invokes "now" multiple times. It would be nicer to do it only once and reuse the value. Something like this: LET right_now = SELECT now () IN SELECT * FROM my_table WHERE right_now >= start AND ... In PL/pgSQL this is easy, but I wonder about SQL... Thanks in advance! Jon --- No, not really. In some cases you can use a CTE (WITH) clause to create a single row with whatever names and values you need and then, using Sub-Selects or CROSS JOIN, introduce that row into the appropriate parts of the query. David J. -- 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] Let-bindings in SQL statements
On 26 January 2012 15:37, Jon Smark wrote: > Hi, > > Is it possible to do the equivalent of let-bindings in a pure SQL function? > I have a SELECT that invokes "now" multiple times. It would be nicer > to do it only once and reuse the value. Something like this: > > LET right_now = SELECT now () IN > SELECT * FROM my_table WHERE right_now >= start AND ... > > In PL/pgSQL this is easy, but I wonder about SQL... > > Thanks in advance! > Jon > > In fact now() is a little bit tricky here. now() returns the time when the transaction started, so if you run `begin;` and call now() multiple times (even in different queries, but within the same transaction), the function will return the same value. regards Szymon
[GENERAL] Let-bindings in SQL statements
Hi, Is it possible to do the equivalent of let-bindings in a pure SQL function? I have a SELECT that invokes "now" multiple times. It would be nicer to do it only once and reuse the value. Something like this: LET right_now = SELECT now () IN SELECT * FROM my_table WHERE right_now >= start AND ... In PL/pgSQL this is easy, but I wonder about SQL... Thanks in advance! Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general