Re: arrays, composite types

# gsstark(at)mit(dot)edu / 2005-09-11 12:11:39 -0400:
> Roman Neuhauser <neuhauser(at)sigpipe(dot)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