Re: arrays, composite types - Mailing list pgsql-general

# 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