March 21st, 2003, 05:12 AM
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?
March 23rd, 2003, 11:37 PM
March 24th, 2003, 12:27 AM
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.
this for more detail.
March 28th, 2003, 02:14 AM
i have the sama problem + or -
I start with the only sql and after i used plpgsql:
CREATE FUNCTION fct_selezione_user_area(text) RETURNS SETOF RECORD AS'
codice_area ALIAS FOR $1;
SELECT INTO objRs_selezione.id_area,objRs_selezione.area * from
tbl_area where area = codice_area;
' LANGUAGE 'plpgsql';
I select the function to see results:
select * from fct_selezione_user_area('B');
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....
March 28th, 2003, 06:02 PM
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 '...
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.
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.