Re: computed values in plpgsql

On Tue, Sep 29, 2009 at 06:30:42PM +0200, Pavel Stehule wrote:
> 2009/9/29 Sam Mason <sam(at)samason(dot)me(dot)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/