Re: [SQL] Function to return a multiple colmn table or view

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend