Thread: [psycopg] Call plpgsql function with an array of custom type
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
On 06/11/2017 03:43 AM, Rory Campbell-Lange wrote:
> 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.
>
>
> 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
[('',)]
>
> Thoughts much appreciated.
>
> Kind regards
> Rory
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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;