Subject: Re: Array of tuples as a parameter, with type casts
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Vladimir Ryabtsev <greatvovan@gmail.com>
Cc: psycopg@lists.postgresql.org
References: 
 <CAMqTPqntVMh1sk6V2GbwqX8ti7RtN2wQLVS-tYfVuVh8qWc4Tw@mail.gmail.com>
 <e2923214-7dfa-7807-5166-80ff468f6dbb@aklaver.com>
 <CAMqTPqk_d9kcuVrEwnu7dY5XKSh7BQQwvUBpKNciTTrhcxnORw@mail.gmail.com>
 <25c3598a-698f-8d0e-3659-791253b903ec@aklaver.com>
Message-ID: <55fa8796-a91b-6560-604c-83dd77b17980@aklaver.com>
Date: Wed, 4 Dec 2019 10:47:18 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.2.1
MIME-Version: 1.0
In-Reply-To: <25c3598a-698f-8d0e-3659-791253b903ec@aklaver.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 12/4/19 10:44 AM, Adrian Klaver wrote:
> On 12/3/19 4:00 PM, Vladimir Ryabtsev wrote:
>> It does not change much, the error message just says it now expects 
>> 'character varying'.
>> Anyway bigint problem is the next in the line.
> 
> Yeah it is related to this:
> 
> https://www.postgresql.org/docs/11/functions-array.html
> unnest(anyarray)     setof anyelement
> 
> Dealing with anyelement can be tricky.
> 
> I tried out an alternate solution:
> 
> json_query = "select * from json_to_recordset(%s) as t(a timestamp, b 
> varchar, c bigint)"
> 
> cur.execute(json_query,[Json([{'a': datetime.now().isoformat(), 'b': 
> 'abc', 'c': 100},{'a': datetime.now().isoformat(), 'b': 'xyz', 'c': 
> 200}])])

Should have added that Json comes from:

from psycopg2.extras import Json

http://initd.org/psycopg/docs/extras.html?highlight=json#json-adaptation


> 
> Which results in:
> 
> test_(postgres)# select * from json_to_recordset(E'[{"a": 
> "2019-12-04T10:40:22.823910", "b": "abc", "c": 100}, {"a": 
> "2019-12-04T10:40:22.823918", "b": "xyz", "c": 200}]') as t(a timestamp, 
> b varchar, c bigint)
> ;
>               a              |  b  |  c
> ----------------------------+-----+-----
>   2019-12-04 10:40:22.82391  | abc | 100
>   2019-12-04 10:40:22.823918 | xyz | 200
> (2 rows)
> 
>>
>> -- 
>> Regards,
>> Vladimir
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com