Re: pl/python composite type array as input parameter

Basically, in an (maybe-over)simplified example:

CREATE OR REPLACE FUNCTION add_transaction(transaction core_transaction)
RETURNS integer AS $$
DECLARE
transaction2 core_transaction;
BEGIN
transaction.field1 := 'lapse’;
transaction2.field2 := transaction.field2;
transaction2.field1 := 'lapse2’;
INSERT INTO core_transaction VALUES(transaction.*);
INSERT INTO core_transaction VALUES(transaction2.*);
RETURN 1;
END
$$
LANGUAGE plpgsql;

So, I wanted to do the same in plpython…

CREATE OR REPLACE FUNCTION add_transaction(transaction core_transaction)
RETURNS integer AS $$
transaction['field1'] = ‘lapse’
transaction2 = { ‘field1’: ‘lapse2’, ‘field2’: transaction[‘field1’] }

# not this but something that would work without enumericating all columns/fields
pl = plpy.execute('INSERT INTO core_transaction VALUES(transaction.*)’)
pl = plpy.execute('INSERT INTO core_transaction VALUES(transaction2.*)')
return 1
END
$$
LANGUAGE plpythonu;

> On 02/06/2015, at 15:51, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 06/02/2015 03:10 AM, Filipe Pina wrote:
>> HI Adrian,
>>
>> I had a typo in the email:
>>
>> INSERT INTO my_table VALUES(my_table.*);
>>
>> was actually
>>
>> INSERT INTO my_table VALUES(my_var.*);
>
> Aah, that is different:)
>
>>
>> So I meant to insert the variable I had in memory (dict representing a row), not the rows from the table..
>
> So where is the variable getting its data?
>
> Or can we see a simple example of what you are trying to do?
>
>>
>>> On 02/06/2015, at 01:44, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>>
>>> On 06/01/2015 07:42 AM, Filipe Pina wrote:
>>>> Thanks for the reply anyway, it's a pity though, it'd be useful..
>>>>
>>>> Another bump I've found along the pl/python road: insert ROWTYPE in table..
>>>> Maybe you have some hint on that? :)
>>>>
>>>> So, in PLPGSQL I can:
>>>>
>>>> DECLARE
>>>> my_var my_table;
>>>> BEGIN
>>>> my_var.col1 := 'asd';
>>>> INSERT INTO my_table VALUES(my_table.*);
>>>> END;
>>>>
>>>> How would I do something like that in pl/python?
>>>>
>>>> First, how to declare a ROW-TYPE variable, as they're all python mappings?
>>>>
>>>> my_var = { 'col1': 'asd' } enough? it'd would miss all the other columns...
>>>>
>>>> Second, how to insert it?
>>>>
>>>> plpy.prepare and .execute say they don't support composite types, so I
>>>> cannot simply pass
>>>>
>>>> pl = plpy.prepare('INSERT INTO core_customer VALUES ($1)', ['my_table'])
>>>>
>>>> Any workarounds for this? (meaning I wouldn't have to specify any
>>>> columns in the insert statement)
>>>
>>> http://www.postgresql.org/docs/9.4/interactive/sql-insert.html
>>>
>>> pl = plpy.prepare('INSERT INTO core_table SELECT * FROM my_table')
>>>
>>>>
>>>> Thanks
>>>>
>>>> On Sex, Mai 29, 2015 at 2:00 , Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>>>>> On 5/18/15 10:52 AM, Filipe Pina wrote:
>>>>>
>>>>> But one of the functions I need to create needs to accept an array
>>>>> of records.
>>>>>
>>>>> PL/Python doesn't support that. Some more code needs to be written to
>>>>> support that. You did everything correctly. I don't know of a good
>>>>> workaround.
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian(dot)klaver(at)aklaver(dot)com
>>
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>