Thread: Function to return a multiple colmn table or view

<p align="LEFT"><span lang="en-ca"><font face="Arial" size="2">I</font></span><span lang="en-ca"></span><span
lang="en-ca"><fontface="Arial" size="2"></font></span><span lang="en-ca"></span><span lang="en-ca"> <font face="Arial"
size="2">amnew to psql</font></span><span lang="en-ca"></span><span lang="en-ca"> <font face="Arial" size="2">, so
pleasebe patient !</font></span><p align="LEFT"><span lang="en-ca"><font face="Arial" size="2">Can someone please
providea small quick example of a a</font></span><span lang="en-ca"></span><span lang="en-ca"><font face="Arial"
size="2"></font></span><spanlang="en-ca"></span><span lang="en-ca"> <font face="Arial" size="2">function that take 1
paramaterand based on that parameter, returns a table or view ?</font></span><span lang="en-ca"></span><span
lang="en-ca"></span><palign="LEFT"><span lang="en-ca"><font face="Arial" size="2">Etc</font></span><span
lang="en-ca"></span><spanlang="en-ca"> <font face="Arial" size="2">…</font></span><span lang="en-ca"></span><span
lang="en-ca"><fontface="Arial" size="2">.</font></span><span lang="en-ca"></span><span lang="en-ca"> <font face="Arial"
size="2">Note this is just a abstract of the functon, not a working function call !!!</font></span><p
align="LEFT"><spanlang="en-ca"><font face="Arial" size="2">Create function viewtest( start_month) </font></span><p
align="LEFT"><spanlang="en-ca">        <font face="Arial" size="2">If</font></span><span lang="en-ca"></span><span
lang="en-ca"><font face="Arial" size="2">$1 =</font></span><span lang="en-ca"></span><span lang="en-ca"> <font
face="Arial"size="2">‘</font></span><span lang="en-ca"></span><span lang="en-ca"><font face="Arial"
size="2">April</font></span><spanlang="en-ca"></span><span lang="en-ca"><font face="Arial"
size="2">’</font></span><spanlang="en-ca"></span><span lang="en-ca"></span><p align="LEFT"><span lang="en-ca">       
<fontface="Arial" size="2">Then </font></span><p align="LEFT"><span lang="en-ca">        <font face="Arial"
size="2">Select* from april_view;</font></span><p align="LEFT"><span lang="en-ca">        <font face="Arial"
size="2">Return;</font></span><p align="LEFT"><span lang="en-ca"><font face="Arial" size="2">Let me know  how to do
this.</font></span><palign="LEFT"><span lang="en-ca"><font face="Arial" size="2">Thanks</font></span><span
lang="en-ca"></span><spanlang="en-ca"></span> 

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 $$
DECLAREsql text;r   record;
BEGINIF startmonth = 1 THEN    sql := 'SELECT * FROM jan_view';ELSE    sql := 'SELECT * FROM month_data';END IF;FOR r
INEXECUTE sql LOOP    RETURN NEXT r;END LOOP;RETURN;
 
END;
$$ LANGUAGE plpgsql;

HTH
--   Richard Huxton  Archonet Ltd

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