CODEWITHSUNDEEP
sqlpostgresqlfunctionplpgsqldynamic-sql
Recomer
Recomer

Reputation: 191

Create Function: Syntax Error at or near $$

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?

Upvotes: 1

Views: 2978

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

Multiple problems. Most importantly, you cannot parameterize identifiers in plain SQL (or anything in DDL commands, for that matter). You need dynamic SQL with EXECUTE in a PL/pgSQL function. This would do the job:

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

Call:

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

Read the manual here, and study some related questions and answers on SO:

Pay special attention to defend against SQL injection properly:

Upvotes: 2

Ctx
Ctx

Reputation: 18420

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';

Upvotes: -2

Related Questions