Re: [SQL] date range to set of dates expansion
Gary Stainburn writes:
> Hi,
>
> How can I expand a date range in a table to a set of date records?
>
> I have a table of availabilities thus:
>
> Column |Type | Modifiers
> ---+-+
> aid | integer | not null default
> nextval('availability_aid_seq'::regclass)
> asid | integer | not null
> asdate| date| not null
> afdate| date| not null
> adays | integer |
> acomments | text|
>
>
> asdate is the start date
> afdate is the finish date
>
> How can I expand this to a set of
>
> Column |Type | Modifiers
> ---+-+
> aid | integer | not null
> asid | integer | not null
> adate| date| not null
> acomments | text|
>
> i.e.
>
> aid | asid | asdate | afdate | adays | acomments
> -+--+++---+
> 12 |1 | 2007-08-11 | 2007-08-12 | 1 | Early finish Sunday
>
> Becomes
>
> aid | asid | asdate | acomments
> -+--++
> 12 |1 | 2007-08-11 | Early finish Sunday
> 12 |1 | 2007-08-12 | Early finish Sunday
>
> I have a function date_range to return a set of dates, but so far I can't get
> a valid view to work.
Why don't you just use the built-in PostgreSQL function for that?
SELECT aid, asid,
generate_series (asdate, afdate, INTERVAL '1 day')::date AS asdate,
acomments
FROM tbl
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] how to return whole table from Function not just the id integer column
hi , how can i return the whole user table from this function not just the id . thanks CREATE TABLE users(id serial PRIMARY KEY, first_name varchar(10), last_name varchar(10)); CREATE OR REPLACE FUNCTION new_user(fname varchar, lname varchar) RETURNS int AS $$ DECLARE r int; BEGIN -- custom exception -- lname cannot be empty or NEMO IF trim(lname) = '' OR lower(lname) = 'nemo' THEN RAISE EXCEPTION 'bad last_name: "%"', lname; END IF; INSERT INTO users(first_name, last_name) VALUES(lname, fname) RETURNING id INTO r; RETURN r; END; $$ LANGUAGE plpgsql;
Re: [SQL] how to return whole table from Function not just the id integer column
On Thu, Jan 19, 2012 at 1:57 AM, Rehan Saleem wrote: > hi , > > how can i return the whole user table from this function not just the id . > thanks > > Chapter 39, specifically 39.3, of the postgresql documentation provides all of the information necessary to answer this question. If, after reading that chapter and attempting to solve the problem yourself, you still have no success, then please post your question to the list along with your best attempt at a solution. You cannot expect to make efficient use of a sophisticated rdbms without first reading at least the most relevant sections of the documentation, and the mailing lists do not have enough experienced users to devote bandwidth to doing your work for you. http://www.postgresql.org/docs/current/static/plpgsql-declarations.html
Re: [SQL] date range to set of dates expansion
On Thursday 19 January 2012 08:32:27 [email protected] wrote: > > Why don't you just use the built-in PostgreSQL function for that? > > SELECT aid, asid, >generate_series (asdate, afdate, INTERVAL '1 day')::date AS asdate, >acomments > FROM tbl 1) because I didn't know about it 2) because the version of postgresql I run doesn't support it. However, it does exactly what I need so thanks very much. I'll be upgrading my live server as soon as possible, but in the meantime can anyone suggest a way I can do the same thing using Postgresql 8.1 until I can evaluate 8.4 on my live systems? -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] date range to set of dates expansion
On Thursday 19 January 2012 15:11:46 Gary Stainburn wrote: > I'll be upgrading my live server as soon as possible, but in the meantime > can anyone suggest a way I can do the same thing using Postgresql 8.1 > until I can evaluate 8.4 on my live systems? Sorry, I meant 8.3 as my current version -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] date range to set of dates expansion
The following code works in 8.4 but not 8.3. Anyone know why, or what I need to do to change it? SELECT aid, asid, date_range (asdate, afdate)::date AS asdate, acomments FROM availability In 8.4 it returns the expanded dataset as required. In 8.3 I get: ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "date_range" line 4 at RETURN NEXT Is there a way to use the integer only generate_series in 8.3 to generate dates by typecasting to/from integers? -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SOLVED - date range to set of dates expansion
Sorry for using the list as a scratch-pad for my brain. select aid, asid, generate_series(asdate-'1970-01-01'::date, afdate-'1970-01-01'::date)+'1970-01-01'::date as adate, acomments from availability; Has done the trick. -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] date range to set of dates expansion
On Thursday, January 19, 2012 7:59:27 am Gary Stainburn wrote: > The following code works in 8.4 but not 8.3. > Anyone know why, or what I need to do to change it? > > SELECT aid, asid, >date_range (asdate, afdate)::date AS asdate, >acomments > FROM availability > > In 8.4 it returns the expanded dataset as required. In 8.3 I get: > > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "date_range" line 4 at RETURN NEXT As to why it works in 8.4 vs 8.3 http://www.postgresql.org/docs/8.4/interactive/release-8-4.html "Support set-returning functions in SELECT result lists even for functions that return their result via a tuplestore (Tom) In particular, this means that functions written in PL/pgSQL and other PL languages can now be called this way.' In 8.3- I believe you could only call it as SELECT * from date_range (asdate, afdate)::date AS asdate; > > Is there a way to use the integer only generate_series in 8.3 to generate > dates by typecasting to/from integers? -- Adrian Klaver [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] date range to set of dates expansion
On 01/19/2012 07:16 AM, Gary Stainburn wrote: On Thursday 19 January 2012 15:11:46 Gary Stainburn wrote: I'll be upgrading my live server as soon as possible, but in the meantime can anyone suggest a way I can do the same thing using Postgresql 8.1 until I can evaluate 8.4 on my live systems? I'm sure most here would recommend moving to 9.1 rather than 8.4. Better performance, cooler replication functionality, more advanced in-place upgrade capabilities for future upgrades, a couple years longer before end-of-life, advances to windowing functions and other SQL commands and much other goodness. Cheers, Steve -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] date range to set of dates expansion
On Thu, Jan 19, 2012 at 8:20 AM, Adrian Klaver wrote: > On Thursday, January 19, 2012 7:59:27 am Gary Stainburn wrote: > > The following code works in 8.4 but not 8.3. > > Anyone know why, or what I need to do to change it? > > > > SELECT aid, asid, > >date_range (asdate, afdate)::date AS asdate, > >acomments > > FROM availability > > > > In 8.4 it returns the expanded dataset as required. In 8.3 I get: > > > > ERROR: set-valued function called in context that cannot accept a set > > CONTEXT: PL/pgSQL function "date_range" line 4 at RETURN NEXT > > As to why it works in 8.4 vs 8.3 > > http://www.postgresql.org/docs/8.4/interactive/release-8-4.html > > "Support set-returning functions in SELECT result lists even for functions > that > return their result via a tuplestore (Tom) > > In particular, this means that functions written in PL/pgSQL and other PL > languages can now be called this way.' > > In 8.3- I believe you could only call it as > > SELECT * from date_range (asdate, afdate)::date AS asdate; > I don't think you can have that cast there when it is in the from-clause. You can refer to its values in the select clause explicitly if you alias the the results: select r.range_date::date from date_range(asfdate, afdate) as r(range_date); Presumably, you can fashion something that satisfies your needs by putting something like this in a subquery which refers to the start and end date in each row of the outer query. I'm in a rush, so no time to figure out a working demo for you. --sam
Re: [SQL] date range to set of dates expansion
On 01/19/2012 09:17 AM, Samuel Gendler wrote: On Thu, Jan 19, 2012 at 8:20 AM, Adrian Klaver mailto:[email protected]>> wrote: On Thursday, January 19, 2012 7:59:27 am Gary Stainburn wrote: > The following code works in 8.4 but not 8.3. > Anyone know why, or what I need to do to change it? > > SELECT aid, asid, >date_range (asdate, afdate)::date AS asdate, >acomments > FROM availability > > In 8.4 it returns the expanded dataset as required. In 8.3 I get: > > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "date_range" line 4 at RETURN NEXT As to why it works in 8.4 vs 8.3 http://www.postgresql.org/docs/8.4/interactive/release-8-4.html "Support set-returning functions in SELECT result lists even for functions that return their result via a tuplestore (Tom) In particular, this means that functions written in PL/pgSQL and other PL languages can now be called this way.' In 8.3- I believe you could only call it as SELECT * from date_range (asdate, afdate)::date AS asdate; I don't think you can have that cast there when it is in the from-clause. That was a cut and paste error on my part, I just copied that line from the original query. -- Adrian Klaver [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
