[GENERAL] arrays, composite types

[Roman Neuhauser]

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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] arrays, composite types

[Greg Stark]

Roman Neuhauser <[EMAIL PROTECTED]> 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


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] arrays, composite types

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2005-09-11 12:11:39 -0400:
> Roman Neuhauser <[EMAIL PROTECTED]> 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
   _setidALIAS FOR $1;
   _arrszALIAS 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
   _setidALIAS FOR $1;
   _arrszALIAS FOR $2;
   _cktypes  ALIAS FOR $3;
   _ckvals   ALIAS FOR $4;
   _pathsALIAS 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org