Create Function: Syntax Error at or near $$

Question

I'm a beginner in Postgres and I want to automate dropping a column from a table with a function. But it gives me the error I mentioned in the title.

Here's my code:

create function dropColumn(table_name text,col_name text) returns void as $$
ALTER TABLE $1 DROP COLUMN IF EXIST $2;
$$
language 'psql';

Error:

ERROR:  syntax error at or near "$$
language 'psql';
create function dropColumn(table_name text,col_name text) returns
void $$"
LINE 1: $$

What's the problem? How can I fix this?

score 3 · Accepted Answer · edited May 23 '17 at 11:44

Almost everything is wrong about your function. Most importantly you cannot parameterize identifiers in plain SQL. You need dynamic SQL with EXECUTE in a plpgsql function (or any other procedural language supporting it). This would do the job:

CREATE OR REPLACE FUNCTION drop_column(table_name text, col_name text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format('ALTER TABLE %I DROP COLUMN IF EXISTS %I'
                 , table_name, col_name);
END
$func$
LANGUAGE plpgsql;

Call:

SELECT drop_column('my_tbl', 'my_column');

Start by reading the manual here and study some of the related questions and answers on SO.

Pay special attention to defend against SQL injection properly:

Table name as a PostgreSQL function parameter

Thank you very much for your detailed answer. I'm going to try this solution and then go through the links you as you've suggested. — Recomer, Jan 03 '16 at 14:27

score -1 · Answer 2 · answered Jan 02 '16 at 22:55

-1

You forgot the keyword AS:

create function dropColumn(table_name text,col_name text) returns void AS $$
ALTER TABLE $1 DROP COLUMN IF EXIST $2;
$$
language 'psql';

answered Jan 02 '16 at 22:55

Ctx

18,090
24
36
51

No, please double-check, the syntax is OK this way. – Ctx Jan 02 '16 at 23:15
Thanks for your help but I just copied the code you wrote but still it gives error.I will restart my terminal and try again. – Recomer Jan 02 '16 at 23:18
It now says language 'psql' doesn't exist. – Recomer Jan 02 '16 at 23:21
Yes, this is not a standard language, you should use `sql` – Ctx Jan 02 '16 at 23:22
But there were other functions that I wrote using this phrase? – Recomer Jan 02 '16 at 23:23
1

`sql` and `plpgsql` are standard languages for postgresql stored procedures. I do not know `psql` in that context (except that the cli-tool has that name). Does it work with `sql`? – Ctx Jan 02 '16 at 23:29
I tried with sql it gave me the error : ERROR: syntax error at or near "$1" LINE 2: ALTER TABLE $1 DROP COLUMN IF EXIST $2; ^ – Recomer Jan 02 '16 at 23:29
That's a different story: You cannot use a table/column name from a variable. Better use `plpgsql`, build the query string and use `execute` – Ctx Jan 02 '16 at 23:35
This answer is almost as completely wrong as the question. There is no language 'psql' to begin with. And you cannot parameterize identifiers in plain SQL statements *at all*. You need dynamic SQL with `EXECUTE` in plpgsql for this ... – Erwin Brandstetter Jan 03 '16 at 03:54
@ErwinBrandstetter The primary question was about the syntax error, which I addressed. Secondly, it would be perfectly possible to create a language `psql` which works as the OP expected. This was out of the scope of this question. Please inform yourself a bit better about postgresql before teaching others. Thanks. – Ctx Jan 03 '16 at 09:15

Create Function: Syntax Error at or near $$

2 Answers2