Re: computed values in plpgsql

On Tue, Sep 29, 2009 at 06:30:42PM +0200, Pavel Stehule wrote:
> 2009/9/29 Sam Mason <sam@samason.me.uk>:
> > I may have got that wrong somewhere else.
>
> I afraid so this technique is very buggy. You need unpacked serialised
> record.

Hum, I'm not sure what an "unpacked serialised record" is or why I'd
need one.

> And the result have to be valid sql literal.

I'm asking PG to generate one for me, and if it doesn't know what a
valid literal is I don't know who does.  Here's a more complete example:

  CREATE TABLE t (name varchar, addr varchar);
  CREATE TABLE s (name varchar, addr varchar);

  CREATE OR REPLACE FUNCTION trig () RETURNS trigger AS $$
    BEGIN
      EXECUTE 'INSERT INTO s (SELECT (t '||quote_literal(new)||').*);';
      RETURN NULL;
    END $$ LANGUAGE plpgsql;

  CREATE TRIGGER trig BEFORE INSERT ON t
    FOR EACH ROW EXECUTE PROCEDURE trig();

  INSERT INTO t VALUES ('Pavel Stehule','Benesov');

  SELECT * FROM s;

This does the right thing for me in both 8.3 and 8.4, it would also seem
as though it's easy to apply this to the problem the OP was having.

> you cannot apply quote literal on two or more columns. I thing, so
> this isn't possible now.

Maybe I mis-interpret the problem?

--
  Sam  http://samason.me.uk/