Stored procedures

Hello.
I'm trying to translate sql code from a asp application to stored procedures in PostgreSQL.
The main problem is when the result of the sql code is a set (recordset) from some joins between two o more tables.
How can I define the function in PostgreSQL for this case? What type I have to select for the result?
Thank you.

have you looked into using views at all?

http://www.phpfreaks.com/postgresql...rial-views.html

If you are trying to return a "setof record" but there isn't a table with the same definition, you can create a composite type instead.

See
this for more detail.

I start with the only sql and after i used plpgsql:

CREATE FUNCTION fct_selezione_user_area(text) RETURNS SETOF RECORD AS'
declare
objRs_selezione RECORD;
codice_area ALIAS FOR $1;
begin
SELECT INTO objRs_selezione.id_area,objRs_selezione.area * from
tbl_area where area = codice_area;
RETURN objRs_selezione;
End;
' LANGUAGE 'plpgsql';

I select the function to see results:
select * from fct_selezione_user_area('B');

error:
A column definition list is required for functions returning RECORD

I start with 2 tables and after i reduce to 1...but the problem is the same...definition of columns....
some idea?
tnx

smauroz,

There's so many things wrong with your function that I don't know where to begin, but I'll try.

First, you cannot return a set of record without defining the record first. As I mentioned previously you either have to have a table that already has the same definition as the record being returned and use it, or define a composite type.

For example, if you have a table:

define table ex1(
id int not null primary key,
foo text not null);

You could use it as the type for the returned set if you were returning an int and text. e.g.

create or replace function bar() returns set of ex1 as '....

if you didn't have a table with that definition but needed to return a set like that you could do this:

create type my_return_type as (id int, foo text);

create or replace function bar() returns set of my_return_type as '...

Second,
Your query inside the function is wrong. You are trying to use a variable (codice_area) inside the select which is problematic. Also, since it is text it would need to be quoted. The proper way to do this is with an EXECUTE.

Third,
Your select into is wrong. For what you are trying it would be:

select into objRs_selezione id_area,area from tbl_area.

I can't give you an example that would work because I'm not even sure what you are trying to return since you threw the * in there.