I'm trying to write a function that will create a new column in a participation table based on the presence of a id in survey_name table:

CREATE OR REPLACE FUNCTION survey_participated(_sn text)
  returns void AS
  EXECUTE format($$ALTER TABLE participation ADD COLUMN %I integer DEFAULT 0$$,_sn);
  EXECUTE format($$UPDATE TABLE participation SET %I = 1 WHERE participation.participant_id = %I.participant_id$$,_sn,_sn);
$func$ LANGUAGE plpgsql;

I keep getting the error:

[2017-10-19 12:38:09] [42601] ERROR: syntax error at or near "TABLE"
[2017-10-19 12:38:09] Where: PL/pgSQL function survey_participated(text) line 4 at EXECUTE

Can someone point out my mistake?

  • update participation set ... (no table keyword) – a_horse_with_no_name Oct 19 '17 at 19:13
  • Having read @ErwinBrandstetter comment I think he's right and I read your query wrong entirely. I think you want a join or EXISTS in that UPDATE – Evan Carroll Oct 20 '17 at 2:45

The first problem is the syntax for UPDATE doesn't permit an optional TABLE after. It's just not needed. UPDATE only operates on tables; ALTER works on many targets. The second problem is that should be %s (literal/string) and not %I (identifier).

EXECUTE format(
  $$ALTER TABLE participation ADD COLUMN %I integer DEFAULT 0;$$,
EXECUTE format(
    UPDATE participation
    SET %I = 1
    WHERE participation.participant_id = quote_literal(%s||participant_id);

That all said, this seems like a horrible idea. You're adding a column to the table, and setting the value of that column to the "colname+id"? How is that useful?

|improve this answer|||||
  • 2
    The problematic part is the keyword table after update – a_horse_with_no_name Oct 19 '17 at 19:14
  • @a_horse_with_no_name missed that, fixed now. ;) – Evan Carroll Oct 19 '17 at 19:16
  • @AlokShenoy because %I will wrap the binding value inside " which is not what you want, you just want it to single-quote escape the value so you can concatenate it with participant_id safely. Assuming _sn = 5, with %I you would get "5"||participant_id referring to the column in the query by the name/alias of "5" – Evan Carroll Oct 19 '17 at 19:50
  • The predicate WHERE participation.participant_id = quote_literal(%s||participant_id); never evaluates to true. Certainly not what the OP wanted, even if he accepted. – Erwin Brandstetter Oct 20 '17 at 2:10
  • 1
    I did accept it because it did point me in the right direction. I think the confusion arose due to the way I had framed the question. @EvanCarrollQWERHJKL, thank you. – Alok Shenoy Oct 21 '17 at 2:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.