Re: pl/python composite type array as input parameter

On 06/02/2015 12:33 PM, Filipe Pina wrote:
> 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;

Yea, I do not see a way of doing that. plpgsql is more tightly coupled
to Postgres then plpythonu, so you get a lot more shortcuts. This why I
tend to use plpgsql even though I prefer programming in Python. That
being said, the feature set of plpythonu has been extended a good deal
over the last couple of Postgres versions and I would expect that to
continue.

>
>
>
>> On 02/06/2015, at 15:51, Adrian Klaver <adrian.klaver@aklaver.com
>> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com
>>>> <mailto:adrian.klaver@aklaver.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@gmx.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.klaver@aklaver.com
>>>
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>


--
Adrian Klaver
adrian.klaver@aklaver.com