Re: pl/python composite type array as input parameter

Thanks Rémi,

Indeed I needed something more generic and easy to maintain (regarding table schema evolution) so I ended up going back to PL/PGSQL (for that specific function) in the meantime.

> On 02/06/2015, at 09:41, Rémi Cura <remi(dot)cura(at)gmail(dot)com> wrote:
>
> OUps,
>
> I forget another strategy I used :
> instead of having
> testp2(es employee[])
>
> you can use
> testp2( names text[], salaries integer[], ages integer[])
>
> This might be the solution with the less work, but it is absolutely terrible practice,
> because it will be hard to change you record type (evolution difficult)
> , and having many columns will make you create function with many arguments,
> which is often a bad idea.
>
> Cheers,
> Rémi-C
>
> 2015-06-02 10:36 GMT+02:00 Rémi Cura <remi(dot)cura(at)gmail(dot)com <mailto:remi(dot)cura(at)gmail(dot)com>>:
> Hey,
> the only straight workaround I know (which is pretty bad)
> is to cast down your record to text.
> Then you have an array of text, which is manageable.
>
> For this you can either 'flatten' your record into a unique text,
> or cast each part of your record to text, then emulate an array of array (you need to know the length of the inner array in your function though).
>
> I used this to emulate a 2D numpy vector (N*3)(for numpy).
>
> You'll need a custom aggregate, like this one <https://github.com/Remi-C/PPPP_utilities/blob/master/postgres/array_of_array.sql>.
>
> The other more sane solution is to pass the information about the row you want to retrieve, and retrieve the row directly within the python.
> For instance, here you would pass an array of id of the employee you want to work with.
> This is saner, but as a design I don't really like to have specific SQL code into a generic python function.
>
> I agree it is cumbersome, and I also badly miss more powerful input for python function (after all, plpython can already return composite types, which is awesome)
>
>
> Cheers,
> Rémi-C
>
> 2015-06-02 2:44 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>:
> 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 <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 <mailto: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 <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general <http://www.postgresql.org/mailpref/pgsql-general>
>
>