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

    Join Date
    Sep 2003
    Rep Power

    Returning custom result sets as string from function

    Hi all,

    I have a question relating to PostgreSQL 7.3's abilities to return result sets from function. I can't use the "setof <table|view|type>" or "setof record" return types, because I don't know anything about the table, which will be used in those queries (table name is got from query in function execution). So the only way to implement this return value is to use simple string return type (key=value, key=value for example). PostgreSQL will support parameters and return values of any types in next version, but I'm using 7.3 now.

    So my question is, how can I loop through all columns of a result set (which has only 1 row), and construct a VARCHAR string with those values, so the resulting string would be of form "column1_name=column1_value, column2_name=column2_value,..." ?

    I know that I can extract all column names from a single table by query:
    SELECT column_name FROM information_schema.columns WHERE table_name = myTableName
    but there's few problems:
    - myTableName is a VARCHAR variable, which VALUE should be used (so I'm listing columns of table, which name is a value of myTableName variable, and not "myTableName")
    - if I manage to get all column names as string, how could I get values of those columns (this is actually the same problem as above, since I can't use "recordset.columnName" notation, because columnName is a string variable containing column name).
    Last edited by Sathamoth; September 18th, 2003 at 04:52 PM.

IMN logo majestic logo threadwatch logo seochat tools logo