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

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 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?
>
> 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 instead of 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()