Adrian Klaver wrote: >>> For the reason why this is happening see: >>> >>> http://www.postgresql.org/docs/9.4/interactive/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING >> >> Yes, but the ALTER TABLE causes the plan to be recreated the next time. > > But does it? From the link above: > > "Because PL/pgSQL saves prepared statements and sometimes execution > plans in this way, SQL commands that appear directly in a PL/pgSQL > function must refer to the same tables and columns on every execution; > that is, you cannot use a parameter as the name of a table or column in > an SQL command. To get around this restriction, you can construct > dynamic commands using the PL/pgSQL EXECUTE statement — at the price of > performing new parse analysis and constructing a new execution plan on > every execution." > > I see '*' as a parameter. Or to put it another way '*' is not referring > to the same thing on each execution when you change the table definition > under the function. Now if I can only get the brain to wake up I could > find the post where Tom Lane explained this more coherently then I can:) Session 1: test=> CREATE TABLE users (id integer PRIMARY KEY, name varchar NOT NULL, to_be_removed integer NOT NULL); CREATE TABLE test=> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS $$BEGIN RETURN QUERY SELECT * FROM users WHERE id = id_; END;$$ LANGUAGE plpgsql; CREATE FUNCTION Session 2: test=> SELECT id, name FROM select_users(18); id | name ----+------ (0 rows) Ok, now the plan is cached. Now in Session 1: test=> ALTER TABLE users DROP COLUMN to_be_removed; ALTER TABLE Session2: test=> SELECT id, name FROM select_users(18); id | name ----+------ (0 rows) No error. This is 9.4.4. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
