[psycopg] Call plpgsql function with an array of custom type - Mailing list psycopg

Hi

I'm prototyping a database interface to be called from Python and PHP in
one of our projects.

The use case is selection grid where 0-many nodes may be selected. The
x-axis is 0-6 (Sunday is 0, Saturday 6) and the y-axis is 0-many, but
typically 0-3. To choose Tuesday, 2nd session you'd select (2, 1) for
instance.

I thought it might be good to use an array of a custom type (called
dow_session) for this use case and other similar ones we have. I realise I
could use integer arrays for this but I'm interested in experimenting with
custom types; SQL at the bottom of the email.

The working (but ugly) postgres SELECT is:

    select * from fn_test01 (
        -- an arbitrary parameter
        num:=1
        -- array of custom type
        ,ds:=ARRAY[(0,0), (1, 3)]::dow_session[]
    );

However I'm having trouble calling this from psycopg.

I've tried:

    In [23]: query = 'select * from fn_test01(%s, %s)'
    In [24]: qargs = (5, [(0,2),])
    In [25]: cur.execute(query, qargs)

    ---------------------------------------------------------------------------
    ProgrammingError  Traceback (most recent call last)
    <ipython-input-25-ace3fd2e2d79> in <module>()
    ----> 1 cur.execute(query, qargs)

    ProgrammingError: function fn_test01(integer, record[]) does not exist
    LINE 1: select * from fn_test01(5, ARRAY[(0, 2)])
                          ^
    HINT:  No function matches the given name and argument types. You might
    need to add explicit type casts.

I can call the function fine if I make it a literal string.

    query = 'select * from fn_test01(12, ARRAY[(0,2)]::dow_session[]);'

But I'd like to avoid that. I've tried

    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.

Thoughts much appreciated.

Kind regards
Rory


SQL:

/* move to test schema */
SET SEARCH_PATH = test;

/* create user defined type */
CREATE TYPE dow_session AS (
    dow INT
    ,session INT
);

/* create test function */
CREATE OR REPLACE FUNCTION fn_test01 (
    num INT
    ,ds dow_session[]
) RETURNS VOID AS $$
DECLARE
    r RECORD;
BEGIN
    RAISE NOTICE 'num: %', num;

    FOREACH r IN ARRAY ds
    LOOP
        RAISE NOTICE '------------';
        RAISE NOTICE 'dow %', r.dow;
        RAISE NOTICE 'sess %', r.session;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

/* call test function */
test=>
     select * from fn_test01 (1, ARRAY[(0,0), (1, 3)]::dow_session[]);

NOTICE:  num: 1
NOTICE:  ------------
NOTICE:  dow 0
NOTICE:  sess 0
NOTICE:  ------------
NOTICE:  dow 1
NOTICE:  sess 3