How to express PostgreSQL variable as a string

Question

Is there any way how to express a variable in PostgreSQL as a string?

Example:

\set table_name countries
SELECT 'SELECT * FROM ' || CAST( :table_name, 'text' ) AS specificQuery;

leads to this error:

ERROR:  syntax error at or near ","
LINE 1: SELECT 'SELECT * FROM ' || CAST( countries, 'text' ) AS specificQuery;

From the line sample above is obvious, that it doesn't convert "countries" to a string, but it is expressed as name of column/table.

How do I convert it?

kgrittn · Accepted Answer · 2012-05-13 21:43:11Z

9

Are you looking for something like this?:

SELECT * FROM :"table_name";

http://www.postgresql.org/docs/current/interactive/app-psql.html#APP-PSQL-VARIABLES

answered May 13, 2012 at 21:43

kgrittn

19.8k4 gold badges41 silver badges47 bronze badges

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

1 Comment

David Over a year ago

Using single quotes also works when you need a string: SELECT 1 WHERE column = :'table_name';. Also for concatenation: SELECT 'abc_' || :'table_name' || '_xyz';.

aleroot · Accepted Answer · 2012-05-13 21:14:38Z

2

Something like this :

SELECT 'SELECT * FROM ' || countries::text AS specificQuery;

answered May 13, 2012 at 21:14

aleroot

73k32 gold badges182 silver badges220 bronze badges

1 Comment

Radek Simko Over a year ago

I'm not sure you've got the original idea... I have countries as a content of a variable. Anyway, I guess you mean table_name::text or :table_name::text, but none of that works.

Collectives™ on Stack Overflow

How to express PostgreSQL variable as a string

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related