From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net>, psycopg(at)postgresql(dot)org |
Subject: | Re: Call plpgsql function with an array of custom type |
Date: | 2017-06-11 14:06:15 |
Message-ID: | f030a725-a8d7-4922-94c3-4d8fb9d5b488@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2017-06-11 15:19:49 | Site search |
Previous Message | Rory Campbell-Lange | 2017-06-11 10:43:52 | Call plpgsql function with an array of custom type |