Postgres Pro
Downloads
Home   >   mailing lists

Thread: Is the use of array as PL/PGSQL function arguments ?

Is the use of array as PL/PGSQL function arguments ?

From
pgsql-ml@baguette.net
Date:
Hello,

Since it's not possible to do a function with a variable number of arguments, I am wondering if it is possible to use
anarray as an argument in a PL/PGSQL function ? 

CREATE OR REPLACE FUNCTION myownfunction(members.id%TYPE[], events.id%TYPE) RETURNS BOOLEAN AS '
  BEGIN
    ...several sql queries that are OK...
    RETURN TRUE;
  END;
' LANGUAGE 'plpgsql';

I am currently getting this error : ERROR:  parser: parse error at or near "["

But I don't see anything in the documentation that forbids the use of array as arguments. So, did I make a mistake or
isit forbidden ? 

Thanks in advance :-)

---------------------------------------
Bruno BAGUETTE (pgsql-ml@baguette.net)

Re: Is the use of array as PL/PGSQL function arguments ?

From
Tom Lane
Date:
pgsql-ml@baguette.net writes:
> Since it's not possible to do a function with a variable number of arguments, I am wondering if it is possible to use
anarray as an argument in a PL/PGSQL function ? 

> CREATE OR REPLACE FUNCTION myownfunction(members.id%TYPE[], events.id%TYPE) RETURNS BOOLEAN AS '

You can certainly use arrays as arguments, but I don't think you can
combine %TYPE with [] like that.

            regards, tom lane

RE : Is the use of array as PL/PGSQL function arguments ?

From
"Bruno BAGUETTE"
Date:
> > CREATE OR REPLACE FUNCTION myownfunction(members.id%TYPE[],
> > events.id%TYPE) RETURNS BOOLEAN AS '
>
> You can certainly use arrays as arguments, but I don't think
> you can combine %TYPE with [] like that.

Thanks Tom for the information ! :-)

When I create functions with %TYPE, PostgreSQL replies that it will
convert the %TYPE with a VARCHAR (for example). So it could replace a
%TYPE[] by a VARCHAR[] (in my example) ?

It seems that this modification could be done easily, I will try to find
in the source code to create this patch. (Without any warranties, I
never done a patch for PostgreSQL).

In the meantime, is it possible to add this feature in the TODO list ?

Regards,

---------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net

Re: RE : Is the use of array as PL/PGSQL function arguments

From
Bruce Momjian
Date:
Added to TODO:

        o Allow PL/pgSQL to handle %TYPE arrays, e.g. tab.col%TYPE[]


---------------------------------------------------------------------------

Bruno BAGUETTE wrote:
> > > CREATE OR REPLACE FUNCTION myownfunction(members.id%TYPE[],
> > > events.id%TYPE) RETURNS BOOLEAN AS '
> >
> > You can certainly use arrays as arguments, but I don't think
> > you can combine %TYPE with [] like that.
>
> Thanks Tom for the information ! :-)
>
> When I create functions with %TYPE, PostgreSQL replies that it will
> convert the %TYPE with a VARCHAR (for example). So it could replace a
> %TYPE[] by a VARCHAR[] (in my example) ?
>
> It seems that this modification could be done easily, I will try to find
> in the source code to create this patch. (Without any warranties, I
> never done a patch for PostgreSQL).
>
> In the meantime, is it possible to add this feature in the TODO list ?
>
> Regards,
>
> ---------------------------------------
> Bruno BAGUETTE - pgsql-ml@baguette.net
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Why I can't combine %TYPE with [] ?

From
"Bruno BAGUETTE"
Date:
Tom Lanes a écrit :

> pgsql-ml@baguette.net writes:
> > Since it's not possible to do a function with a variable number of
> > arguments, I am wondering if it is possible to use an array as an
> > argument in a PL/PGSQL function ?
>
> > CREATE OR REPLACE FUNCTION myownfunction(members.id%TYPE[],
> > events.id%TYPE) RETURNS BOOLEAN AS '
>
> You can certainly use arrays as arguments, but I don't think
> you can combine %TYPE with [] like that.

I've checked in the PostgreSQL 7.3.4 sources
(backend/parser/parse_type.c) and especially the char *
TypeNameToString(const TypeName *typename) function.

At the end of this function it seems that manages %TYPE that is combined
with [] :

       if (typename->arrayBounds != NIL)
                appendStringInfo(&string, "[]");

so I'm asking why I can't write a PL/PGSQL function like

CREATE OR REPLACE FUNCTION myownfunction(members.id%TYPE[],
events.id%TYPE) RETURNS BOOLEAN AS '

Am I looking at the wrong place in the sources ?

Regards,

---------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net

Re: Why I can't combine %TYPE with [] ?

From
Bruce Momjian
Date:
We just added this to TODO:

        o Allow PL/pgSQL to handle %TYPE arrays, e.g. tab.col%TYPE[]

but that will help TYPE inside a function, not as part of the function
parameter list.

Do I need a TODO for this, or is it listed under some other item?

---------------------------------------------------------------------------

Bruno BAGUETTE wrote:
> Tom Lanes a ?crit :
>
> > pgsql-ml@baguette.net writes:
> > > Since it's not possible to do a function with a variable number of
> > > arguments, I am wondering if it is possible to use an array as an
> > > argument in a PL/PGSQL function ?
> >
> > > CREATE OR REPLACE FUNCTION myownfunction(members.id%TYPE[],
> > > events.id%TYPE) RETURNS BOOLEAN AS '
> >
> > You can certainly use arrays as arguments, but I don't think
> > you can combine %TYPE with [] like that.
>
> I've checked in the PostgreSQL 7.3.4 sources
> (backend/parser/parse_type.c) and especially the char *
> TypeNameToString(const TypeName *typename) function.
>
> At the end of this function it seems that manages %TYPE that is combined
> with [] :
>
>        if (typename->arrayBounds != NIL)
>                 appendStringInfo(&string, "[]");
>
> so I'm asking why I can't write a PL/PGSQL function like
>
> CREATE OR REPLACE FUNCTION myownfunction(members.id%TYPE[],
> events.id%TYPE) RETURNS BOOLEAN AS '
>
> Am I looking at the wrong place in the sources ?
>
> Regards,
>
> ---------------------------------------
> Bruno BAGUETTE - pgsql-ml@baguette.net
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

RE : Why I can't combine %TYPE with [] ?

From
"Bruno BAGUETTE"
Date:
> We just added this to TODO:
>
>         o Allow PL/pgSQL to handle %TYPE arrays, e.g. tab.col%TYPE[]
>
> but that will help TYPE inside a function, not as part of the
> function parameter list.
>
> Do I need a TODO for this, or is it listed under some other item?
>

Hello,

I don't see anything in the TODO about the handle of %TYPE arrays in the
function parameter list. I think that this should be added in the TODO
also.

Regards :-)

---------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net