CODEWITHSUNDEEP
sqlpostgresql
Senju
Senju

Reputation: 1035

PL/PgSQL treats variable name as column name in CREATE VIEW

I have a function similar to this:

CREATE FUNCTION func(TEXT) RETURNS TEXT AS $$
DECLARE
v_name1 ALIAS FOR $1;

BEGIN

SELECT * from table1 WHERE names_column = v_name1;
RETURN v_name1;

END;
$$ LANGUAGE plpgsql;

If I do

SELECT func('Alex');

It should run:

SELECT * from table1 WHERE names_column = 'Alex';

But instead I get this error:

ERROR:  column "v_name1" does not exist
LINE 2: SELECT * FROM table1 WHERE names_column = v_name1;

How do I tell Postgres to get the TEXT inside the variable to compare?

[EDIT]This is the function I have so far that produces the error:

CREATE OR REPLACE FUNCTION get_champ(end_date DATE, champ_name TEXT) RETURNS TEXT AS $$
DECLARE
new_champ_name TEXT;
id_of_new_champ INTEGER;
date_of_new_champ DATE;
BEGIN

CREATE OR REPLACE VIEW champ AS
SELECT * FROM basketball2 WHERE visitor = champ_name OR home = champ_name;

SELECT MIN(id) INTO id_of_new_champ FROM champ WHERE winner <> champ_name;
SELECT date INTO date_of_new_champ FROM basketball2 WHERE id = id_of_new_champ;
SELECT winner INTO new_champ_name FROM basketball2 WHERE id = id_of_new_champ;
RETURN new_champ_name;

END;
$$ LANGUAGE plpgsql;

Upvotes: 2

Views: 678

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324375

CREATE OR REPLACE VIEW is not a plannable statement in PostgreSQL, it's DDL.

This means it cannot accept query parameters. So PL/PgSQL's normal translation of PL/PgSQL variables into query parameters is not available. It takes the query literally as written.

To get the result you want, you must use dynamic SQL with EXECUTE, e.g.

 EXECUTE format('CREATE OR REPLACE VIEW champ AS
 SELECT * FROM basketball2 WHERE visitor = %L OR home = %L', champ_name, champ_name);

... but in this case, creating a view in the first place appears to be nonsensical. Why would you do that, it gains you nothing whatsoever. Just write:

SELECT MIN(id) 
INTO id_of_new_champ 
FROM basketball2 
WHERE (visitor = champ_name OR home = champ_name)
  AND winner <> champ_name;

Also, never "simplify" or "anonymize" your code without clearly saying you have done so in the question, and *testing that the problem still happens with the simplified code. Otherwise you're just wasting your time and everybody else's.

Upvotes: 4

Related Questions