2008 4GB AJAX announced ARAX August available Basic cpu date dotNet download DVD edition exe express final firewall function glue instance Katmai large laser manage management manufacturing method microsoft NET oracle pages pipes Redmond reference release remote return returning route RTM Ruby server setup SQL sqlserver studio version windows XML

How do I create stored procedure or function on PostgreSQL that returns dataset

So you decided to try PostgreSQL, the most advanced open source database, and you are used to microsoft SQL server and would like to be able to program stored procedures that return datasets. At first you assume that it will be easy but not all is the way it seems. With Microsoft SQL server you could, for example, return record from table A, or a record from table B if no record in table A is found. If tables are not of same definition you are out of luck with PostgreSQL. The Postgres engine requires well defined return values so you always must declare type returned. Obviously having type that fits both A and B would not be possible if tables are different. Luckily, most stored procedures on SQL server are well structured and always return same set of data types. Those can be easily converted.

Enter default table types. For example, imagine, that you created table called test. You can run select * from test but what if you want that to be done in stored procedure? Well, first of all, you need stored function, not procedure. You can choose from selection of languages, but for this example, we will use SQL language as it is most readily available on all installations even if add-on languages are not installed. Your function should look something like:

CREATE OR REPLACE FUNCTION public.testfunc () RETURNS test AS
'
SELECT * FROM test
'
LANGUAGE 'sql'

Be careful as PostgreSQL is case sensitive, so if you define table Test, selecting from test will yield an error. Ok, now you have a function, so how do you get the dataset? EXEC does not exist on PostgreSQL in same fashion as it does on SqlServer, so you use SELECT * FROM testfunc() and you are done.

More info:

permanent link to article http://freecash.hogger.net/how_do_i_create_stored_procedure_or_function_on_postgresql_that_returns_dataset