Re: Trying to understand why same SQL returns different results.
От | Neil Tiffin |
---|---|
Тема | Re: Trying to understand why same SQL returns different results. |
Дата | |
Msg-id | 36EDC837-E977-467B-B4D9-25535214B201@neiltiffin.com обсуждение исходный текст |
Ответ на | Re: Trying to understand why same SQL returns different results. (Adrian Klaver <adrian.klaver@gmail.com>) |
Список | psycopg |
On Nov 20, 2012, at 2:43 PM, Adrian Klaver wrote: > On 11/20/2012 12:33 PM, Neil Tiffin wrote: >> When I run the SQL directly from psycopg and from a plpgsql function each produces different results. One returns twocolumns 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? > > Expected. > In the first case you are returning two columns. > In the second a single record. > > Running in psql explains it better: > > First case: > > test=> SELECT 'some text' AS colume1, 'some more text' as column2 ; > colume1 | column2 > -----------+---------------- > some text | some more text > (1 row) > > > Second case: > > test=> SELECT get_some_text(); > get_some_text > -------------------------------- > ("some text","some more text") > (1 row) > Should have thought to test that. Using both comments, I was able to understand how to make both calls the same. The function should return a table insteadof a record. Which, after the fact, makes a lot more sense. Thanks Adrian and Jason. For other that follow, the solution was as follows: def sql_test1(): cur = CONN.cursor() cur.execute(""" DROP FUNCTION get_some_text(); CREATE OR REPLACE FUNCTION get_some_text() RETURNS TABLE(column1 varchar, column2 varchar) AS $$ DECLARE BEGIN RETURN QUERY SELECT 'some text'::varchar AS colume1, 'some more text'::varchar as column2; 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 column1, column2 FROM get_some_text();""") print (cur.description) print (cur.fetchone()) CONN.commit() cur.close()
В списке psycopg по дате отправления:
Предыдущее
От: Neil TiffinДата:
Сообщение: Re: Trying to understand why same SQL returns different results.