PostgreSQL: Syntax error at or near "||"

Question

I am creating function which return the select query result from it. The details as shown below in the example:

Example:

Create or replace function fun_test(cola text,colb text,rel text)
returns table(columna text,columnb text)as 
$Body$
Declare
table_name varchar :='Table_';
Begin
table_name := table_name || rel;

    return query select distinct || quote_ident(cola) ||,||quote_ident(colb)|| from || quote_ident(table_name) ;

end;
$Body$
language plpgsql;

Error:

ERROR:  syntax error at or near "||"
LINE 10: ...| quote_ident(cola) ||,||quote_ident(colb)|| from || quote_i...
                                                              ^

Oops, it looks like you reposted it to dba.stackexchange.com/q/69417/7788. Please delete the duplicate. — Craig Ringer
– Craig Ringer, Commented Jul 1, 2014 at 6:28

Craig Ringer · Accepted Answer · 2014-07-01 06:33:44Z

2

You're trying to construct a query dynamically, but you're not using EXECUTE. That won't work. You can't just put arbitrary expressions in place of identifiers, like:

return query select distinct || quote_ident(cola) ||,||quote_ident(colb)|| from || quote_ident(table_name) ;

which is why you're getting the error at:

from ||
     ^

as that's syntactically invalid nonsense.

Instead I think you want:

RETURN QUERY EXECUTE 'select distinct ' || quote_ident(cola) ||', '||quote_ident(colb)||' from '|| quote_ident(table_name);

which is better written as:

RETURN QUERY EXECUTE format('select distinct %I, %I from %I', cola, colb, table_name);

answered Jul 1, 2014 at 6:33

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

2 Comments

Sarfaraz Makandar Over a year ago

Great! But can we use select into by using EXECUTE?

Craig Ringer Over a year ago

@Meem EXECUTE ... INTO. Please read the manual, it's all there.

Collectives™ on Stack Overflow

PostgreSQL: Syntax error at or near "||"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related