Why doesn't postgres accept function argument as table name

Question

I'm trying the update a table using the function below. The table name is a function argument. Running this function results in the error:

ERROR: syntax error at or near "$1" LINE 1: (SELECT * FROM $1 ORDER BY $2 )

I tried using the EXECUTE statement and the quote_ident function, but without success sofar. I'm sure I'm overlooking something simple...

CREATE OR REPLACE FUNCTION createdefaultorder(table_name varchar, sort_column varchar)
  RETURNS integer AS
$BODY$
DECLARE
  rRec RECORD;
  counter integer := 0;
BEGIN
  FOR rRec IN  (SELECT * FROM table_name ORDER BY sort_column)  LOOP
    UPDATE table_name SET row_number = counter WHERE id = rRec.id;
    counter := counter + 1;
  END LOOP; 
  RETURN 0;
END;
$BODY$
  LANGUAGE plpgsql;

Please either accept an answer or update your question. The code you provided does not appear relevant to the error you posted at the top. — Seldom 'Where's Monica' Needy
– Seldom 'Where's Monica' Needy, Commented Dec 9, 2015 at 22:47

Peter Eisentraut · Accepted Answer · 2011-04-26 10:08:02Z

5

Using EXECUTE and quote_ident is the correct solution. If you have trouble with that, you might want to show that code.

answered Apr 26, 2011 at 10:08

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

donnut Over a year ago

Thanks for pointing me in the right direction. I managed the get the required result with the following function: ` BEGIN FOR rRec IN EXECUTE 'SELECT * FROM ' || quote_ident(table_name) || ' ORDER BY ' || quote_ident(sort_column) LOOP EXECUTE 'UPDATE ' || quote_ident(table_name) || ' SET row_number = ' || counter || ' WHERE id = ' || rRec.id; counter := counter + 1; END LOOP; RETURN 0; END; `

rmcc · Accepted Answer · 2011-04-28 14:45:19Z

0

Execute and quote_ident are the solution to that problem: PostgreSQL 8.4 Manual

answered Apr 28, 2011 at 14:45

rmcc

7831 gold badge6 silver badges15 bronze badges

Comments

john igneel · Accepted Answer · 2015-07-21 07:47:17Z

0

You must use EXECUTE and quote_ident() in a dynamic query execution like this:

BEGIN FOR rRec IN EXECUTE 'SELECT * FROM ' || quote_ident(table_name) || ' ORDER BY ' || quote_ident(sort_column) LOOP EXECUTE 'UPDATE ' || quote_ident(table_name) || ' SET row_number = ' || counter || ' WHERE id = ' || rRec.id; counter := counter + 1; END LOOP; RETURN 0; END;

answered Jul 21, 2015 at 7:47

john igneel

4171 gold badge6 silver badges18 bronze badges

Collectives™ on Stack Overflow

Why doesn't postgres accept function argument as table name

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related