#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    1
    Rep Power
    0

    Question 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.
  2. #2
  3. Second highest poster :p
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jul 2001
    Posts
    7,322
    Rep Power
    33
    have you looked into using views at all?

    http://www.phpfreaks.com/postgresqlm...ial-views.html
    - Andreas Koepke

    Koepke Photography

  4. #3
  5. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    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.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    6
    Rep Power
    0

    Unhappy 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'
    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
  8. #5
  9. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    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.

IMN logo majestic logo threadwatch logo seochat tools logo