PostgreSQL: ERROR: syntax error at or near "-"

Question

Here I need to call a function with some parameters.

Example:

Function:

create or replace function testfunction(ids int,pcname varchar)
returns void as
$$
declare
       sql varchar;
       qu varchar := 'tabletemp_';
begin
       qu := qu ||ids ||'_'|| pcname;
       sql := 'Drop view if exists '||qu;

       raise info '%',sql;
       execute sql;
 end;
 $$
 language plpgsql;

Calling Function:

select testfunction(1,'abc-pc');

Error:

ERROR:  syntax error at or near "-"
Drop view if exists tabletemp_1_abc-pc
                                   ^

Question: How can I pass such parameter while calling function?

@Meem do you think select from testfunction(1,'abc-pc');is correct? correct it — Vivek S.
– Vivek S., Commented Jul 23, 2014 at 8:41

Craig Ringer · Accepted Answer · 2014-07-23 07:59:47Z

2

You forgot to quote_ident.

   sql := 'Drop view if exists '|| quote_ident(qu);

but you should preferably use format:

   sql := format('Drop view if exists %I', qu || ids ||'_'|| pcname);

answered Jul 23, 2014 at 7:59

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

Comments

Ilesh Patel · Accepted Answer · 2014-07-23 08:03:03Z

2

Try This:

create or replace function testfunction(ids int,pcname varchar)
returns void as
$$
declare
       sql varchar;
       qu varchar := 'tabletemp_';
begin
       qu := qu ||ids ||'_'|| pcname;
       sql := 'Drop view if exists "'||qu||'"';

       raise info '%',sql;
       execute sql;
 end;
 $$
 language plpgsql;

Here I add double quote around table name.

answered Jul 23, 2014 at 8:03

Ilesh Patel

2,15516 silver badges27 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: ERROR: syntax error at or near "-"

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related