[SQL] Using new.id in the WHERE condition of one UPDATE inside ON INSERT rule

[Aron]

I would like to avoid using "SELECT MAX(id) FROM my_table" (it doesn't seem a 
good method), but if I use "new.id", I get new id values, not the "id" 
inserted with the rule, and the condition is always false.

Complete example (it works because it doesn't use new.id inside condition):

DROP TABLE IF EXISTS my_table;
DROP TABLE IF EXISTS my_other_table;

CREATE TABLE my_other_table (
id serial PRIMARY KEY,
my_other_cost INTEGER
);

INSERT INTO my_other_table(my_other_cost) VALUES(155);
INSERT INTO my_other_table(my_other_cost) VALUES(277);

CREATE TABLE my_table (
id serial PRIMARY KEY,
id_other INTEGER,
my_cost INTEGER
);

CREATE OR REPLACE RULE my_insert AS
ON INSERT TO my_table
DO ALSO
UPDATE my_table SET my_cost = my_other_table.my_other_cost
FROM my_other_table
WHERE new.id_other = my_other_table.id
AND my_table.id = (SELECT MAX(id) FROM my_table); -- I want " = 
new.id" 
here, but doesn't work as I expect

INSERT INTO my_table(id_other) VALUES(1);
INSERT INTO my_table(id_other) VALUES(2);

SELECT * FROM my_table;


Thanks
-- 


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Using new.id in the WHERE condition of one UPDATE inside ON INSERT rule

[Tom Lane]

Aron  writes:
> I would like to avoid using "SELECT MAX(id) FROM my_table" (it doesn't seem a 
> good method), but if I use "new.id", I get new id values, not the "id" 
> inserted with the rule, and the condition is always false.

"new.id" is a macro, which in this example will be expanded into a
nextval() function call, which is why it doesn't work --- the nextval()
in the WHERE condition will produce a different value from the one in
the original INSERT.  You would be far better off using a trigger here
instead of a rule.

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] what exactly is a query structure?

[silly sad]

hello.

Postgresql 8.3.9

CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT);

CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$
BEGIN
  RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob;
  RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

SELECT * from get_noobs();

And we have the following error

ERROR:  structure of query does not match function result type

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] what exactly is a query structure?

[A. Kretschmer]

In response to silly sad :
> hello.
> 
> Postgresql 8.3.9
> 
> CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT);
> 
> CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$
> BEGIN
>   RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob;
>   RETURN;
> END;
> $$ LANGUAGE plpgsql SECURITY DEFINER;
> 
> SELECT * from get_noobs();
> 
> And we have the following error
> 
> ERROR:  structure of query does not match function result type

Wild guess: your table noob has an other structure as expected, in
particular login and/or shop_pass are not TEXT.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql