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

    Join Date
    Oct 2003
    Posts
    7
    Rep Power
    0

    how to create a multi column return function


    Hi

    I'm moving databases from sybase to postgres.
    But I have difficulties in creating a postgres equivalent to the sybase stored procedures...

    Apparently, Postgres functions should work, but the syb stored procedures get only one parameter and return several colums

    Here's the code I wrote in postgresql :

    create function function_name( int ) returns text
    AS ' SELECT column1, column2, column3,...,column15
    FROM table_name
    WHERE colum1 = $1 AND column5 = \'specific value\' AND column8 = \'specific_value2 \' '
    LANGUAGE 'SQL';

    and I get the message error : returns multi columns

    I'm wondering too if It's possible to create a view with a parameter if functions don't work.

    Has anybody faced the same problem ?

    I need help

    thanks
  2. #2
  3. Prom night: 1973
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Dec 2001
    Posts
    1,156
    Rep Power
    24
    I think your problem is that you're returning many columns but you've declared a return type of TEXT. I'm not sure exactly what you want to return based on your function, but Postgres allows you to return a row based on a table's structure, in which case you'd want to do:

    CREATE FUNCTION function_name(INT) RETURNS table_name%ROWTYPE

    or you can create a custom type if the data you're returning isn't specific to a table. In which case you'd have to do:

    CREATE TYPE my_data_type AS (
    column1 INT,
    column2 INT,
    [insert more definitions here]
    column15 TEXT
    )

    and then

    CREATE FUNCTION function_name(INT) RETURNS my_data_type

    If you want to return more than one row, you can just add "SET OF" to your definition (e.g. RETURNS SET OF table_name%ROWTYPE)

    Or, I can't think why you'd want to, but you could concatenate everything together into a string to return it all as text. I'd rethink things if that's what you want to do. It would be terrible design.

    The Postgres docs aren't great when it comes to stored procedures, but hunt around in http://techdocs.postgresql.org/ for examples. I'd start at http://techdocs.postgresql.org/guide...rningFunctions

    I'm still learning about views, so I can't answer your other question. I didn't know any of this a month ago, so it's not so hard to find out Good luck.

IMN logo majestic logo threadwatch logo seochat tools logo