Обсуждение: arrays, composite types

I'm looking for an equivalent of my_composite_type[] for use as a
parameter of a pl/pgsql function. What do people use to dodge this
limitation?

Background: I have a few plpgsql functions that basically accept an
array of objects decomposed into arrays of the objects' attributes:

 CREATE FUNCTION do_foo(int4, int4[], int4[], varchar[]) RETURNS int4 VOLATILE

which I'd like to convert to

 CREATE FUNCTION do_foo(int4, myctype[]) RETURNS int4 VOLATILE

so that the functions only need recompilation when myctype changes.

myctype is

 CREATE TYPE myctype AS (a int4, b int4, c varchar)

Ideally, what I'm looking for will work in plpgsql, but I'm ok with
writing a bit or two in C as long as it can be made short,
selfcontained, and bugfree (crashing PostgreSQL or wrong data would be
enough rope to hang myself on I'm afraid).

BTW, I don't see arrays of composite types in the TODO, and the ability
to specify composite types indirectly through schema.rel.attr%TYPE isn't
there either. Are these two features out of the question for some
reason?

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

Roman Neuhauser <neuhauser@sigpipe.cz> writes:

> I'm looking for an equivalent of my_composite_type[] for use as a
> parameter of a pl/pgsql function. What do people use to dodge this
> limitation?
>
> Background: I have a few plpgsql functions that basically accept an
> array of objects decomposed into arrays of the objects' attributes:

What do you want to do with these arrays? Why do you want to work with them in
plpgsql?

When you get to this point I think I would start looking at using plperl and
using Dumper to store the objects in a text column. You're trading off
database normalization against being able to express arbitrarily complex data
structures.

--
greg

# gsstark@mit.edu / 2005-09-11 12:11:39 -0400:
> Roman Neuhauser <neuhauser@sigpipe.cz> writes:
>
> > I'm looking for an equivalent of my_composite_type[] for use as a
> > parameter of a pl/pgsql function. What do people use to dodge this
> > limitation?
> >
> > Background: I have a few plpgsql functions that basically accept an
> > array of objects decomposed into arrays of the objects' attributes:
>
> What do you want to do with these arrays? Why do you want to work with
> them in plpgsql?

    See this pseudocode, demonstrating the desired implementation:

    CREATE DOMAIN cksum_d AS VARCHAR(n)
     CONSTRAINT dom_cksum CHECK (VALUE IN ('CRC32', ...));

    CREATE TYPE cksum_t AS (
     cktype myschema.cksum_d,
     ckval  INTEGER
    );

    CREATE TYPE fprops AS (
     bytes INTEGER,
     cksum myschema.cksum_t,
     path  VARCHAR(n)
     ...
    );

    CREATE TABLE filesets (
     id SERIAL,
     ...
    );

    CREATE TABLE files (
     id SERIAL,
     setid INTEGER NOT NULL,
     props fprops
     FK setid -> filesets.id
    );

    Now I need to replace one or more records in files with a different
    one. That's done with:

    CREATE FUNCTION replace_files(int, int, fprops[])
     RETURNS INTEGER VOLATILE STRICT AS
     'DECLARE
       _setid    ALIAS FOR $1;
       _arrsz    ALIAS FOR $2;
       _newfiles ALIAS FOR $3;
       _cnt      INTEGER DEFAULT 1;
      BEGIN
       DELETE FROM files where setid = _setid;
       WHILE _cnt <= _arrsz LOOP
         INSERT INTO files (setid, props) VALUES (_setid, _newfiles[_cnt]);
         _cnt := _cnt + 1;
       END LOOP;
      END;
    ';

    Except the function actually does more, and contains (should
    contain)

     PERFORM SELECT other_function(_setid, _newfiles[_cnt]);

    or similar, and there's a handful of functions that the values pass
    through. As it is, I need to change the signature and body of all
    these functions whenever I need to add another field to the
    (effective) structure files, and I of course want to avoid that.

    It's just like passing pointers to structures as function arguments
    in C, this helps preserve source code compatibility.

    I have working code, it's just ugly:

    CREATE FUNCTION replace_files(int, int, varchar[], int[], varchar[], ...)
     RETURNS INTEGER VOLATILE STRICT AS
     'DECLARE
       _setid    ALIAS FOR $1;
       _arrsz    ALIAS FOR $2;
       _cktypes  ALIAS FOR $3;
       _ckvals   ALIAS FOR $4;
       _paths    ALIAS FOR $5;
       _cnt      INTEGER DEFAULT 1;
       DELETE FROM files where setid = _setid;
       WHILE _cnt <= _arrsz LOOP
         INSERT INTO files (setid, props)
          VALUES (_setid, _cktypes[_cnt], _ckvals[_cnt], _paths[_cnt], ...);
         _cnt := _cnt + 1;
       END LOOP;
      END;
     ';

> When you get to this point I think I would start looking at using plperl

    I'd like to avoid switching to a "big" language: it's quite late in
    the release cycle, and this is a commercial product. I cannot tell
    our sales the next version will be three or four months late.

> and using Dumper to store the objects in a text column. You're trading
> off database normalization against being able to express arbitrarily
> complex data structures.

    That doesn't fit my needs at all, but thanks for thinking about my
    problem!

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991