[SQL] Table returning functions
Hi, I have a question which does not seem to be covered in the documentation: I have a function f(i) that returns a table with three columns (a, b, c). In the documentation (http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET) it says that (even though deprecated) you can call such functions in a SELECT clause like so: SELECT id, f(id) FROM some_table . This will give me a two column table that looks like this: id | (a, b, c) However since I want a 4 column table like this id | a | b | c , I do this instead: SELECT id, (f(id)).a, (f(id)).b, (f(id)).c FROM some_table. However I'm not quite sure if this really gives me what I want (i.e. the same as in the first query only with the tuple elements as separate columns). Of course I'm open to suggestions on how to do this more elegantly and also how one can do something like this without using the deprecated feature of calling a set returning function in SELECT. Jann -- 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] Generating Rows from a date and a duration
Hi Brian, Hi List, At Sat, 4 Sep 2010 09:20:53 -0400, Brian Sherwood wrote: > On Fri, Sep 3, 2010 at 5:40 AM, Tim Schumacher < > tim.daniel.schumac...@gmail.com> wrote: > > > I'm kinda stuck situation, I have a timestamp which resambles a > > startdate and a duration in days and I want to bloat this, so I have a > > row for every day beginning from the startdate. I have created an > > example bellow, maybe I'm doing it on the wrong angle and you can come > > up with some better ideas: > > > > BEGIN TRANSACTION; > > > > CREATE TABLE example > > ( > > id serial NOT NULL, > > startdate timestamp without time zone, > > duration int_unsigned NOT NULL, > > CONSTRAINT pq_example_id PRIMARY KEY (id) > > ) WITH (OIDS=FALSE) > > ; > > > > insert into example(id,startdate,duration) values (1,'2010-09-03',4); > > insert into example(id,startdate,duration) values (2,'2010-09-03',6); > > > > CREATE OR REPLACE FUNCTION bloat_duration(IN id integer, > > IN startdate timestamp > > without time zone, > > IN duration integer, > > OUT id integer, > > OUT duration_date date) > >RETURNS SETOF RECORD AS > > $$ > > BEGIN > > RETURN QUERY SELECT > > id,to_date(to_char(startdate,'-MM-DD'),'-MM-DD')+s.a AS > > stockdate FROM generate_series(0,duration-1) AS s(a); > > END; > > $$ > > LANGUAGE 'plpgsql'; > > > > -- This works, but not what I want > > SELECT * FROM bloat_duration(1,'2010-09-03',4); > > > > -- This does not work > > > > SELECT * FROM example AS ex > > INNER JOIN bloat_duration(ex.id,ex.startdate,ex.duration) AS bd ON bd.id > > = ex.id > > > > ROLLBACK TRANSACTION; > Take a look at the generate_series function in the "set returning functions" > section of the manual. > http://www.postgresql.org/docs/8.4/interactive/functions-srf.html As you can see in my example, I'm already using it and this is my dilemma. Since I can not bring the values of the FROM-Table to the parameters of my function. Greetings Tim -- Compassion -- that's the one things no machine ever had. Maybe it's the one thing that keeps men ahead of them. -- McCoy, "The Ultimate Computer", stardate 4731.3 -- 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] Generating Rows from a date and a duration
On 09/08/2010 08:24 AM, Tim Schumacher wrote: >>> I'm kinda stuck situation, I have a timestamp which resambles a >>> startdate and a duration in days and I want to bloat this, so I have a >>> row for every day beginning from the startdate. I have created an >>> example bellow, maybe I'm doing it on the wrong angle and you can come >>> up with some better ideas: > As you can see in my example, I'm already using it and this is my > dilemma. Since I can not bring the values of the FROM-Table to the > parameters of my function. Depending on how large your base table is, this might work for you: CREATE TABLE example ( id serial NOT NULL, startdate timestamp without time zone, duration int NOT NULL, CONSTRAINT pq_example_id PRIMARY KEY (id) ); insert into example(id,startdate,duration) values (1,'2010-09-03',4); insert into example(id,startdate,duration) values (2,'2010-09-03',6); CREATE OR REPLACE FUNCTION unroll_durations() RETURNS TABLE( example_id integer, duration_date date) AS $$ DECLARE rec1 record; rec2 record; BEGIN FOR rec1 IN SELECT id, startdate, duration FROM example LOOP FOR rec2 IN SELECT to_date(to_char(rec1.startdate,'-MM-DD'),'-MM-DD') + s.a as stockdate FROM generate_series(0, rec1.duration - 1) AS s(a) LOOP example_id:= rec1.id; duration_date := rec2.stockdate; RETURN NEXT; END LOOP; END LOOP; END; $$ LANGUAGE plpgsql; select * from unroll_durations(); example_id | duration_date +--- 1 | 2010-09-03 1 | 2010-09-04 1 | 2010-09-05 1 | 2010-09-06 2 | 2010-09-03 2 | 2010-09-04 2 | 2010-09-05 2 | 2010-09-06 2 | 2010-09-07 2 | 2010-09-08 (10 rows) HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support signature.asc Description: OpenPGP digital signature
