pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction to return a multiple colmn table or view
3+ messages / 2 participants
[nested] [flat]
* Function to return a multiple colmn table or view
@ 2007-04-20 14:13 Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Wilkinson, Jim @ 2007-04-20 14:13 UTC (permalink / raw)
To: pgsql-sql
I am new to psql , so please be patient !
Can someone please provide a small quick example of a a function that
take 1 paramater and based on that parameter, returns a table or view ?
Etc .... Note this is just a abstract of the functon, not a working
function call !!!
Create function viewtest( start_month)
If $1 = 'April'
Then
Select * from april_view;
Return ;
Let me know how to do this.
Thanks
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Function to return a multiple colmn table or view
@ 2007-04-20 14:32 Richard Huxton <dev@archonet.com>
parent: Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2007-04-20 14:32 UTC (permalink / raw)
To: Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>; +Cc: pgsql-sql
Wilkinson, Jim wrote:
> I am new to psql , so please be patient !
>
> Can someone please provide a small quick example of a a function that
> take 1 paramater and based on that parameter, returns a table or view ?
>
> Etc .... Note this is just a abstract of the functon, not a working
> function call !!!
>
> Create function viewtest( start_month)
>
> If $1 = 'April'
> Then
> Select * from april_view;
> Return ;
CREATE TABLE month_data (md_year int4, md_month int4, md_data text);
CREATE VIEW jan_view AS SELECT * FROM month_data WHERE md_month=1;
CREATE FUNCTION viewtest(startmonth int) RETURNS SETOF month_data AS $$
DECLARE
sql text;
r record;
BEGIN
IF startmonth = 1 THEN
sql := 'SELECT * FROM jan_view';
ELSE
sql := 'SELECT * FROM month_data';
END IF;
FOR r IN EXECUTE sql LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
HTH
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Function to return a multiple colmn table or view
@ 2007-04-24 15:52 Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2007-04-24 15:52 UTC (permalink / raw)
To: Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>; pgsql-sql
Don't forget to cc: the list
Wilkinson, Jim wrote:
> Hi Richard, your example worked find , but when I substitue my view into
> the function and the tale name, I get the following error:
>
> ERROR: wrong record type supplied in RETURN NEXT
> CONTEXT: Pl/pqSQL function "create_view" line 11 at return next
The columns you're returning from the function don't match it's definition.
If the function is defined as RETURNS SETOF X then it needs to return
columns that match X. If you need to you can create a custom type (see
"CREATE TYPE" in the manuals).
If you want one function that returns different types then you can't
have it.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-04-24 15:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-20 14:13 Function to return a multiple colmn table or view Wilkinson, Jim <Jim.Wilkinson@cra-arc.gc.ca>
2007-04-20 14:32 ` Richard Huxton <dev@archonet.com>
2007-04-24 15:52 Re: Function to return a multiple colmn table or view Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox