September 18th, 2003, 03:45 PM
Returning custom result sets as string from function
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:
but there's few problems:
SELECT column_name FROM information_schema.columns WHERE table_name = myTableName
- 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.