Re: Trying to understand why same SQL returns different results.

On Nov 20, 2012, at 2:38 PM, Jason Lubken wrote:

> Neil,
>
> I think you need:
>
>
> select column1, column2 from get_come_text();

Traceback (most recent call last):
File "server.py", line 1219, in <module>
sql_test1()
File "server.py", line 919, in sql_test1
cur.execute("""SELECT column1, column2 FROM get_some_text();""")
psycopg2.ProgrammingError: a column definition list is required for functions returning "record"
LINE 1: SELECT column1, column2 FROM get_some_text();

Thank you, but that did not seem to work. Now that I know it's not a bug, I continue looking for the magic syntax.

Neil

> … It doesn't automagically split the columns out of the returned row.
>
>
> Jason
>
> On Nov 20, 2012, at 3:33 PM, Neil Tiffin wrote:
>
>> When I run the SQL directly from psycopg and from a plpgsql function each produces different results. One returns two columns and the other only returns one column.
>>
>> For example.
>>
>> def sql_test1():
>> cur = CONN.cursor()
>> cur.execute("""
>> CREATE OR REPLACE FUNCTION get_some_text()
>> RETURNS RECORD AS $$
>> DECLARE
>> result RECORD;
>> BEGIN
>> SELECT 'some text' AS colume1, 'some more text' as column2 INTO result;
>> RETURN result;
>> END;
>> $$ LANGUAGE plpgsql;""")
>>
>> print('test 1')
>> cur.execute("""SELECT 'some text' AS colume1, 'some more text' as column2;""")
>> print (cur.description)
>> print (cur.fetchone())
>>
>> print ('test 2')
>> cur.execute("""SELECT get_some_text();""")
>> print (cur.description)
>> print (cur.fetchone())
>>
>> CONN.commit()
>> cur.close()
>>
>> Output:
>> test 1
>> (Column(name='colume1', type_code=705, display_size=None, internal_size=-2, precision=None, scale=None, null_ok=None), Column(name='column2', type_code=705, display_size=None, internal_size=-2, precision=None, scale=None, null_ok=None))
>> ('some text', 'some more text')
>>
>> test 2
>> (Column(name='get_some_text', type_code=2249, display_size=None, internal_size=-1, precision=None, scale=None, null_ok=None),)
>> ('("some text","some more text")',)
>>
>> Is this the expected results and I am misunderstanding something? Or is this a bug?
>>
>> Neil
>>
>> Python 3.3.0
>> Mac OS X 10.7.5, Postgresql 9.1.2, psycopg git clone,
>>
>> --
>> Sent via psycopg mailing list (psycopg(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/psycopg
>