From now on I'll try to use the sample database Pagila .
I will now create a stored procedure for getting all customers in that database belonging to a specific (inparameter) store.
Stored procedures are probably the harest thing to grasp if you are used to SQL Server like I am. The thing about it is that the return value(s) must be defined. I am used to do things like
select a1.id, a2.namn from a1, a2 ...
however in Postgresql the return statement must be defined - i.e a knows type and in my example it's pretty easy - we want to return a set of customer - and "customer" is defined since it's a table.
CREATE OR REPLACE FUNCTION getcustomersbystore(p_in_storeid integer)
RETURNS SETOF customer AS
'select * from customer where store_id=$1'
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION getcustomersbystore(p_in_storeid integer) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION getcustomersbystore(p_in_storeid integer) TO public;
GRANT EXECUTE ON FUNCTION getcustomersbystore(p_in_storeid integer) TO postgres;
