Re: Function to return a multiple colmn table or view - Mailing list 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 $$
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