Message-ID: <4628CEFC.7020801@archonet.com>
Date: Fri, 20 Apr 2007 15:32:28 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: "Wilkinson, Jim" <Jim.Wilkinson@cra-arc.gc.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Function to return a multiple colmn table or view
References: <7CCC5BEF5E72394C963E529B54EB4A642A19F1@SD01ITMV12.PROD.NET>
In-Reply-To: <7CCC5BEF5E72394C963E529B54EB4A642A19F1@SD01ITMV12.PROD.NET>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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