Обсуждение: Trying to understand why same SQL returns different results.

Поиск
Список
Период
Сортировка

Trying to understand why same SQL returns different results.

От
Neil Tiffin
Дата:
When I run the SQL directly from psycopg and from a plpgsql function each produces different results.  One returns two
columnsand 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,

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

От
Adrian Klaver
Дата:
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

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

От
Neil Tiffin
Дата:
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
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?
>>
>> Neil
>>
>> Python 3.3.0
>> Mac OS X 10.7.5, Postgresql 9.1.2, psycopg git clone,
>>
>> --
>> Sent via psycopg mailing list (psycopg@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/psycopg
>

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

От
Neil Tiffin
Дата:
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()