Re: [SQL] SQL syntax rowcount value as an extra column in the result set
Hi, >It works, but you should use a recent version: >test=*# select count(1) over (), i from foo; > count | i >---+ > 8 | 1 > 8 | 2 > 8 | 3 > 8 | 6 > 8 | 7 > 8 | 9 > 8 | 13 > 8 | 14 >(8 rows) > test=*# select version(); >version > > PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real > (Debian 4.3.2-1.1) 4.3.2, 64-bit > (1 row) Thank you for setting that right. Apologies for not checking version. Is this approach better compared to postgres=# select * from (select count(*) from people ) p, (select firstname from people)p2; count | firstname ---+--- 5 | Mary 5 | Mary 5 | John 5 | John 5 | Jacob (5 rows) This gives me postgres=# explain select * from (select count(*) from people )as p, (select firstname from people)p2; QUERY PLAN - Nested Loop (cost=14.00..30.42 rows=320 width=226) -> Aggregate (cost=14.00..14.01 rows=1 width=0) -> Seq Scan on people (cost=0.00..13.20 rows=320 width=0) -> Seq Scan on people (cost=0.00..13.20 rows=320 width=218) Since I don't have 8.4, I am not in a position to do explain on that version. My guess - over () will be better. My query does sequential scans/nested loop...(if there are no indexes) Regards, Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] Help me with this multi-table query
In response to Nilesh Govindarajan : > Hi, > > I want to find out the userid, nodecount and comment count of the userid. > > I'm going wrong somewhere. > > Check my SQL Code- Check my example: test=*# select * from u; id 1 2 3 (3 rows) test=*# select * from n; uid - 1 1 1 2 2 3 (6 rows) test=*# select * from c; uid - 1 2 2 3 3 3 (6 rows) test=*# select foo.id, foo.n_count, count(c) from (select u.id, count(n) as n_count from u right join n on (n.uid=u.id) group by 1) foo left join c on foo.id=c.uid group by id, n_count; id | n_count | count +-+--- 1 | 3 | 1 2 | 2 | 2 3 | 1 | 3 (3 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] SQL syntax rowcount value as an extra column in the result set
In response to Jayadevan M : > Hi, > I don't think so. > Oracle - > SQL> select count(*) over () as ROWCOUNT , first_name from people; > > ROWCOUNT FIRST_NAME > -- > - > --- > 6 Mary > 6 Mary > 6 John > 6 John > 6 John > 6 Jacob > > 6 rows selected. > > PostgreSQL > postgres=# select count(*) over () as ROWCOUNT , first_name from people; > ERROR: syntax error at or near "over" > LINE 1: select count(*) over () as ROWCOUNT , first_name from people... It works, but you should use a recent version: test=*# select count(1) over (), i from foo; count | i ---+ 8 | 1 8 | 2 8 | 3 8 | 6 8 | 7 8 | 9 8 | 13 8 | 14 (8 rows) test=*# select version(); version PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2, 64-bit (1 row) test=*# Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] SQL syntax rowcount value as an extra column in the result set
Hi, I don't think so. Oracle - SQL> select count(*) over () as ROWCOUNT , first_name from people; ROWCOUNT FIRST_NAME -- - --- 6 Mary 6 Mary 6 John 6 John 6 John 6 Jacob 6 rows selected. PostgreSQL postgres=# select count(*) over () as ROWCOUNT , first_name from people; ERROR: syntax error at or near "over" LINE 1: select count(*) over () as ROWCOUNT , first_name from people... ^ Regards, Jayadevan From: Thomas Kellerer To: pgsql-sql@postgresql.org Date: 26/03/2010 03:26 Subject:Re: [SQL] SQL syntax rowcount value as an extra column in the result set Sent by:pgsql-sql-ow...@postgresql.org Snyder, James wrote on 25.03.2010 22:33: > I’m using PostgreSQL (8.4.701) There is no such version. The current version is 8.4.3 > On a side note, Oracle allows the following syntax to achieve the above: > > select count(*) over () as ROWCOUNT , first_name from people > The same syntax will work on Postgres Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] SQL syntax rowcount value as an extra column in the result set
Hi, Is this what you are trying to do? postgres=# select * from (select count(*) from people ) p, (select firstname from people)p2; count | firstname ---+--- 5 | Mary 5 | Mary 5 | John 5 | John 5 | Jacob (5 rows) I do not know about the performance impact of such a query (cartesian join) Regards, Jayadevan From: "Snyder, James" To: Date: 26/03/2010 03:21 Subject:[SQL] SQL syntax rowcount value as an extra column in the result set Sent by:pgsql-sql-ow...@postgresql.org Hello I’m using PostgreSQL (8.4.701) and Java (jdbc, postgresql-8.4-701.jdbc4.jar) to connect to the database. My question is: what is the SQL syntax for PostgreSQL to achieve the following: I want to receive the rowcount along with the rest of a result set. For example, let’s say the following query returns select first_name from people; first_name = Mary Sue Joe and the following query returns the value select count(*)as ROWCOUNT from people; ROWCOUNT == 3 3 What I’m looking for is the output as ROWCOUNT , first_name = 3 , Mary 3 , Sue 3 , Joe so I can use JDBC (snip-it) as follows: resultSet.getInt(“ROWCOUNT”) resultSet.getString(“first_name”) On a side note, Oracle allows the following syntax to achieve the above: select count(*) over () as ROWCOUNT , first_name from people Thanks,Jim DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] SQL syntax rowcount value as an extra column in the result set
Snyder, James wrote on 25.03.2010 22:33: I’m using PostgreSQL (8.4.701) There is no such version. The current version is 8.4.3 On a side note, Oracle allows the following syntax to achieve the above: select count(*) over () as ROWCOUNT , first_name from people The same syntax will work on Postgres Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] SQL syntax rowcount value as an extra column in the result set
Hello I'm using PostgreSQL (8.4.701) and Java (jdbc, postgresql-8.4-701.jdbc4.jar) to connect to the database. My question is: what is the SQL syntax for PostgreSQL to achieve the following: I want to receive the rowcount along with the rest of a result set. For example, let's say the following query returns select first_name from people; first_name = Mary Sue Joe and the following query returns the value select count(*)as ROWCOUNT from people; ROWCOUNT == 3 3 What I'm looking for is the output as ROWCOUNT , first_name = 3 , Mary 3 , Sue 3 , Joe so I can use JDBC (snip-it) as follows: resultSet.getInt("ROWCOUNT") resultSet.getString("first_name") On a side note, Oracle allows the following syntax to achieve the above: select count(*) over () as ROWCOUNT , first_name from people Thanks,Jim
Re: [SQL] Dollar quoted strings
Hi John, The type code 2003 indicates an SQL99 column type of ARRAY, which is not currently supported for rendering/editing. Here is the feature request for SQuirreL SQL Client to track this feature: https://sourceforge.net/tracker/?func=detail&aid=2972937&group_id=28383&atid=393417 Rob On Wed, Mar 24, 2010 at 4:38 PM, John Gage wrote: > Thanks very much for this. I am using 8.4.2. > > This query works as you describe in pgAdmin. > > I had tried it in the SquirrelSQL client, which is where it produced the > > > > result. > > Obviously, this is a SquirrelSQL problem at least in part. > > What does mean? > > Thanks again for directing me to the doc's on dollar quoting. > > John > > > On Mar 24, 2010, at 8:13 PM, Josh Kupershmidt wrote: > >> On Wed, Mar 24, 2010 at 2:38 PM, John Gage wrote: >>> >>> In going through the arcana of string functions, I have come across the >>> following series of selects that contain, for me, a mysterious "$re$". >>> >>> -- return all matches from regexp >>> SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$); >> >> The $re$ is just an arbitrary identifier for a dollar-quoted string >> constant. See: >> >> http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html >> >> Maybe you're on an old version which doesn't support dollar-quoted >> strings? >> >> On 8.3 for the above query, I get: >> >> SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$); >> regexp_matches >> >> {bar,beque} >> (1 row) >> >> Josh > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Dollar quoted strings
Thanks very much for this. I am using 8.4.2. This query works as you describe in pgAdmin. I had tried it in the SquirrelSQL client, which is where it produced the result. Obviously, this is a SquirrelSQL problem at least in part. What does mean? Thanks again for directing me to the doc's on dollar quoting. John On Mar 24, 2010, at 8:13 PM, Josh Kupershmidt wrote: On Wed, Mar 24, 2010 at 2:38 PM, John Gage wrote: In going through the arcana of string functions, I have come across the following series of selects that contain, for me, a mysterious "$re $". -- return all matches from regexp SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$); The $re$ is just an arbitrary identifier for a dollar-quoted string constant. See: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html Maybe you're on an old version which doesn't support dollar-quoted strings? On 8.3 for the above query, I get: SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$); regexp_matches {bar,beque} (1 row) Josh -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Help with reg_exp
[I mistakenly used the wrong Subject line initially with this post] In going through the arcana of string functions, I have come across the following series of selects that contain, for me, a mysterious "$re$". -- return all matches from regexp SELECT regexp_matches(' foobarbequebaz', $re$(bar)(beque)$re$); -- test case insensitive SELECT regexp_matches('foObARbEqUEbAz', $re$(bar)(beque)$re$, 'i'); -- global option - more than one match SELECT regexp_matches('foobarbequebazilbarfbonk', $re$(b[^b]+)(b[^b]+)$re$, 'g'); When I run this I get: regexp_matches -- I have not been able to find out what it all means. Forgive me for my blindness. John P.S. The author of the statements is "alexk" at Command Prompt. They are test statements against Postgres' string functions.
[SQL] Help me with this multi-table query
Hi, I want to find out the userid, nodecount and comment count of the userid. I'm going wrong somewhere. Check my SQL Code- select u.uid, count(n.nid) nc , count(c.cid) cc from users u left join node n on ( n.uid = u.uid ) left join comments c on ( c.uid = u.uid ) group by u.uid having u.uid <> 0 order by u.uid; This gives me the output like this - uid | nc | cc -+---+--- 1 | 14790 | 14790 4 | 2684 | 2684 19 | 1170 | 1170 24 |80 |80 29 | 119 | 119 33 |64 |64 36 | 9 | 0 41 |78 |78 42 | 7 | 0 43 | 2 | 0 44 | 2 | 2 50 | 2 | 0 55 | 0 | 0 58 | 0 | 0 60 | 0 | 0 73 | 0 | 0 75 | 0 | 0 76 | 0 | 0 81 | 0 | 0 82 | 0 | 0 85 | 0 | 0 86 | 0 | 0 88 | 0 | 0 90 | 0 | 0 91 | 0 | 0 92 | 0 | 0 93 | 0 | 0 94 | 0 | 0 95 | 0 | 0 (29 rows) Whereas, the output for the individual count queries - 1. select u.uid, count(n.nid) nc from users u left join node n on ( n.uid = u.uid ) group by u.uid having u.uid <> 0 order by u.uid; 2. select u.uid, count(c.nid) cc from users u left join comments c on ( c.uid = u.uid ) group by u.uid having u.uid <> 0 order by u.uid; are as follows - uid | nc -+- 1 | 174 4 | 61 19 | 65 24 | 20 29 | 17 33 | 16 36 | 9 41 | 26 42 | 7 43 | 2 44 | 2 50 | 2 55 | 0 58 | 0 60 | 0 73 | 0 75 | 0 76 | 0 81 | 0 82 | 0 85 | 0 86 | 0 88 | 0 90 | 0 91 | 0 92 | 0 93 | 0 94 | 0 95 | 0 (29 rows) uid | cc -+ 1 | 85 4 | 44 19 | 18 24 | 4 29 | 7 33 | 4 36 | 0 41 | 3 42 | 0 43 | 0 44 | 1 50 | 0 55 | 0 58 | 0 60 | 0 73 | 0 75 | 0 76 | 0 81 | 0 82 | 0 85 | 0 86 | 0 88 | 0 90 | 0 91 | 0 92 | 0 93 | 0 94 | 0 95 | 0 (29 rows) Something is seriously wrong. I want nc and cc in just one query. How to ? -- Nilesh Govindarajan Site & Server Administrator www.itech7.com -- 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] Does IMMUTABLE property propagate?
Greg Stark writes: > On Thu, Mar 25, 2010 at 2:35 PM, Tom Lane wrote: >> When you're intending to have a SQL function be inlined, it's probably >> best not to mark it as either IMMUTABLE or STRICT --- that doesn't buy >> anything and it can complicate matters as to whether inlining is legal. > I'm confused, I thought it was volatile and strict that prevented inlining. No: we won't inline if the contained expression is more volatile than what the function is marked as being. This is a hack that prevents the inlining logic from defeating kluges that people might be using in certain applications, namely putting "immutable" or "stable" wrapper functions around functions that are more volatile than that in the eyes of the system. You can do that to force the planner to treat things as immutable/stable in certain contexts; but of course the trick wouldn't work if the inliner opens up the function and exposes its true contents. But in the other direction, exposing a definition that is less volatile than the function's declaration cannot break anything. Similarly, a STRICT marking prevents inlining unless the planner can prove that the contained expression would act the same as the function declaration w.r.t. returning null for any null input; and in all but the simplest cases it can't prove that. regards, tom lane -- 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] Does IMMUTABLE property propagate?
2010/3/25 Louis-David Mitterrand : > On Thu, Mar 25, 2010 at 08:27:27AM +0100, Pavel Stehule wrote: >> 2010/3/25 Louis-David Mitterrand : >> > On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: >> >> Petru Ghita writes: >> >> > "..immediately replaced with the function value" doesn't mean that the >> >> > results of a previously evaluated function for the same parameters are >> >> > stored and reused? >> >> >> >> No, it means what it says: the function is executed once and replaced >> >> with a constant representing the result value. >> > >> > So for example a function like: >> > >> > >> > CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS >> > text >> > AS $$ >> > declare >> > outtext text; >> > begin >> > outtext = trim(regexp_replace(intext, E'\\s*Short( >> > Break)?', '', 'i')); >> > return outtext; >> > end; >> > $$ >> > LANGUAGE plpgsql; >> > >> >> yes it should be declared as immutable. plpgsql function is black box >> for executor, so you have to use some flag. language sql is different, >> executor see inside, so there you can not do it. > > Hmm, that's interesting. So for simple functions (like my example) it is > better to write them in plain sql? And in that case no 'immutable' flag > is necessary? > sure Pavel > -- > 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
[SQL] Plpgsql: Iterating through a string of parameters
I'm struggling with how to make plpgsql iterate through a list of numbers input as a text string, eg. "1438 2656 973 4208". I figure that I can use the regexp_split_to_array() function to make an array of the string, but can I iterate through an array with eg. a FOR loop? regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- 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] Does IMMUTABLE property propagate?
On Thu, Mar 25, 2010 at 2:35 PM, Tom Lane wrote: > When you're intending to have a SQL function be inlined, it's probably > best not to mark it as either IMMUTABLE or STRICT --- that doesn't buy > anything and it can complicate matters as to whether inlining is legal. I'm confused, I thought it was volatile and strict that prevented inlining. -- greg -- 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] Plpgsql: Iterating through a string of parameters
On Thursday 25. March 2010 16.16.53 Leif Biberg Kristensen wrote: > I'm struggling with how to make plpgsql iterate through a list of numbers > input as a text string, eg. "1438 2656 973 4208". I figure that I can use the > regexp_split_to_array() function to make an array of the string, but can I > iterate through an array with eg. a FOR loop? I found a solution myself through trial-and-error: CREATE OR REPLACE FUNCTION text_to_arr(TEXT) RETURNS VOID AS $$ DECLARE arr TEXT ARRAY; BEGIN arr := regexp_split_to_array($1, E'\\s+'); FOR i IN 1..array_length(arr, 1) LOOP RAISE NOTICE '%', arr[i]::INTEGER; END LOOP; RETURN; END $$ LANGUAGE plpgsql IMMUTABLE; pgslekt=> select * from text_to_arr('1438 2607 1504'); NOTICE: 1438 NOTICE: 2607 NOTICE: 1504 text_to_arr - (1 row) pgslekt=> regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- 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] Does IMMUTABLE property propagate?
Louis-David Mitterrand writes: > Hmm, that's interesting. So for simple functions (like my example) it is > better to write them in plain sql? And in that case no 'immutable' flag > is necessary? If it's just a simple SQL expression, then yes write it as a SQL function. The planner can "inline" those, eliminating the call overhead that you'll pay with a plpgsql function. When you're intending to have a SQL function be inlined, it's probably best not to mark it as either IMMUTABLE or STRICT --- that doesn't buy anything and it can complicate matters as to whether inlining is legal. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Capacity planning.
Hi, Any way to get transaction count from the postgres daemon or any log? Also where can I find docs that can help me make a capacity plan for max 100,000 clients making around 200 transactions a day each. -- Regards. David Harel, == Home office +972 77 7657645 Cellular: +972 54 4534502 Snail Mail: Amuka D.N Merom Hagalil 13802 Israel Email: harel...@ergolight-sw.com
Re: [SQL] Week to date function
On 25 March 2010 12:25, Ireneusz Pluta wrote: > Hello, > > is there any standard function, or a concise solution based on set of them, > returning a set of dates included in a week of given year and week number? > I ended up with creating my own function as in the example below, but I am > curious if I am not opening an open door. Try to think of something like this? SELECT date_trunc('week', '2010-01-01'::date) + '12 week'::interval + (d::text||' day')::interval FROM generate_series(0, 6) AS d; > > Thanks > > Irek. > > CREATE OR REPLACE FUNCTION week2date(double precision, double precision) > RETURNS SETOF date > AS > $_$ > SELECT day > FROM ( > SELECT to_char(day, 'IYYY')::integer AS iyyy, > to_char(day, 'IW' )::integer AS iw, > day > FROM ( > SELECT start + generate_series(0, n) AS day > FROM ( > SELECT start, > (stop - start)::integer AS n > FROM ( > SELECT (to_date($1::text, ''::text) - interval > '3 days')::date AS start, > (to_date($1::text, ''::text) + interval '1 year > 3 days')::date AS stop > ) ss > ) aa > ) bb > ) cc > WHERE iw = $2 AND iyyy = $1 > ORDER > BY day > $_$ > LANGUAGE SQL > IMMUTABLE > ; > > SELECT week2date(date_part('year', now()), date_part('week', now())); > week2date > > 2010-03-22 > 2010-03-23 > 2010-03-24 > 2010-03-25 > 2010-03-26 > 2010-03-27 > 2010-03-28 > (7 rows) > > SELECT week2date(2009, 53); > week2date > > 2009-12-28 > 2009-12-29 > 2009-12-30 > 2009-12-31 > 2010-01-01 > 2010-01-02 > 2010-01-03 > (7 rows) > > SELECT week2date(2010, 1); > week2date > > 2010-01-04 > 2010-01-05 > 2010-01-06 > 2010-01-07 > 2010-01-08 > 2010-01-09 > 2010-01-10 > (7 rows) > > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Week to date function
Hello, is there any standard function, or a concise solution based on set of them, returning a set of dates included in a week of given year and week number? I ended up with creating my own function as in the example below, but I am curious if I am not opening an open door. Thanks Irek. CREATE OR REPLACE FUNCTION week2date(double precision, double precision) RETURNS SETOF date AS $_$ SELECT day FROM ( SELECT to_char(day, 'IYYY')::integer AS iyyy, to_char(day, 'IW' )::integer AS iw, day FROM ( SELECT start + generate_series(0, n) AS day FROM ( SELECT start, (stop - start)::integer AS n FROM ( SELECT (to_date($1::text, ''::text) - interval '3 days')::date AS start, (to_date($1::text, ''::text) + interval '1 year 3 days')::date AS stop ) ss ) aa ) bb ) cc WHERE iw = $2 AND iyyy = $1 ORDER BY day $_$ LANGUAGE SQL IMMUTABLE ; SELECT week2date(date_part('year', now()), date_part('week', now())); week2date 2010-03-22 2010-03-23 2010-03-24 2010-03-25 2010-03-26 2010-03-27 2010-03-28 (7 rows) SELECT week2date(2009, 53); week2date 2009-12-28 2009-12-29 2009-12-30 2009-12-31 2010-01-01 2010-01-02 2010-01-03 (7 rows) SELECT week2date(2010, 1); week2date 2010-01-04 2010-01-05 2010-01-06 2010-01-07 2010-01-08 2010-01-09 2010-01-10 (7 rows)
Re: [SQL] Does IMMUTABLE property propagate?
On Thu, Mar 25, 2010 at 08:27:27AM +0100, Pavel Stehule wrote: > 2010/3/25 Louis-David Mitterrand : > > On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: > >> Petru Ghita writes: > >> > "..immediately replaced with the function value" doesn't mean that the > >> > results of a previously evaluated function for the same parameters are > >> > stored and reused? > >> > >> No, it means what it says: the function is executed once and replaced > >> with a constant representing the result value. > > > > So for example a function like: > > > > > > CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS > > text > > AS $$ > > declare > > outtext text; > > begin > > outtext = trim(regexp_replace(intext, E'\\s*Short( Break)?', > > '', 'i')); > > return outtext; > > end; > > $$ > > LANGUAGE plpgsql; > > > > yes it should be declared as immutable. plpgsql function is black box > for executor, so you have to use some flag. language sql is different, > executor see inside, so there you can not do it. Hmm, that's interesting. So for simple functions (like my example) it is better to write them in plain sql? And in that case no 'immutable' flag is necessary? -- 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] Does IMMUTABLE property propagate?
2010/3/25 Louis-David Mitterrand : > On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: >> Petru Ghita writes: >> > "..immediately replaced with the function value" doesn't mean that the >> > results of a previously evaluated function for the same parameters are >> > stored and reused? >> >> No, it means what it says: the function is executed once and replaced >> with a constant representing the result value. > > So for example a function like: > > > CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS > text > AS $$ > declare > outtext text; > begin > outtext = trim(regexp_replace(intext, E'\\s*Short( Break)?', > '', 'i')); > return outtext; > end; > $$ > LANGUAGE plpgsql; > yes it should be declared as immutable. plpgsql function is black box for executor, so you have to use some flag. language sql is different, executor see inside, so there you can not do it. Regards Pavel Stehule > could/should be declared immutable? > > Thanks, > > -- > 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