Search Postgresql Archives

Re: pl/python composite type array as input parameter

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxx>
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@xxxxxxxxxxx




--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>



--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux