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

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)

>
> Neil
>
> Python 3.3.0
> Mac OS X 10.7.5, Postgresql 9.1.2, psycopg git clone,
>


--
Adrian Klaver
adrian.klaver@gmail.com