Re: [psycopg] Call plpgsql function with an array of custom type

On 11/06/17, Adrian Klaver (adrian.klaver@aklaver.com) wrote:
> On 06/11/2017 03:43 AM, Rory Campbell-Lange wrote:
> >I'm prototyping a database interface to be called from Python and PHP in
> >one of our projects.
...
> >
> >     register_composite('test.dow_session', cur)
> >
> >But:
> >     query = 'select * from fn_test01(%s, %s)'
> >
> >     qargs = (5, "[[0,2]::test.dow_session]")
> >     cur.execute(query, qargs)
> >     DETAIL:  "[" must introduce explicitly-specified array dimensions.
> >
> >     qargs = (5, "[[0,2]]::test.dow_session")
> >     cur.execute(query, qargs)
> >     DETAIL:  "[" must introduce explicitly-specified array dimensions.
> >
> >     qargs = (5, ["(0,2)::test.dow_session"])
> >     cur.execute(query, qargs)
> >     ProgrammingError: function fn_test01(integer, text[]) does not exist
> >
> >all don't work.
>
>
> query = 'select * from fn_test01(%s, %s::dow_session[])'
> qargs = (5, ["(0,2)", "(1, 3)"])
> cur.execute(query, qargs)
> rs = cur.fetchall()
> rs
> [('',)]

Hi Adrian

That works perfectly. Thank you so much!

    In [108]: query = 'select * from fn_test03(%s, %s::dow_session[])'
    In [109]: qargs = (5, [(0,2), (1, 3)])
    In [110]: cur.execute(query, qargs)
    In [111]: rs = cur.fetchall()
    In [112]: rs
    Out[112]: [(0, 2), (1, 3)]

With regards
Rory

p.s.

For the record here is a slightly modified test setup.

/* create type in postgres */
CREATE TYPE dow_session AS (
    dow INT
    ,session INT
);


/* create test function in postgres */
CREATE OR REPLACE FUNCTION fn_test03 (
    num INT
    ,ds dow_session[]
) RETURNS SETOF dow_session AS $$
DECLARE
    r dow_session;
BEGIN
    FOREACH r IN ARRAY ds
    LOOP
        RETURN NEXT r;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;