Postgresql format() function alternative

Question

Hi My question is already asked here

But i haven't found any answer there in that link. I don't want any tool to acheive my requirement. I am looking for a simple procedure or query. In that link someone answered about search_columns() procedure. Right now i am using postgresql 9.0.4 version. Can some Postgresql Guru over there can answer this problem

Also Is there any alternative for format() function for 9.1 below versions. I see that the format() function is supported in postresql 9.1 versions and above. Can anybody tell me how to use format function or any alternative to format function for 9.0 or below versions.

Thanks in advance

What about this answer: stackoverflow.com/a/5350405/330315

user330315
– user330315

2014-11-20 07:01:53 +00:00
Commented Nov 20, 2014 at 7:01 — user330315
– user330315, Commented Nov 20, 2014 at 7:01

Craig Ringer · Accepted Answer · 2014-11-21 00:48:47Z

1

On old versions you must instead use quote_ident and quote_literal with concatenation operations.

e.g.

format('SELECT %I FROM %I WHERE %I = %L', col1, tbl, col2, val)

becomes

'SELECT '||quote_ident(col1)||' FROM '||quote_ident(tbl)||' WHERE '||quote_ident(col2)||' = '||quote_nullable(val);

edited Nov 21, 2014 at 0:48

answered Nov 20, 2014 at 6:49

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

1 Comment

pozs Over a year ago

Almost perfect, but %L should be replaced by quote_nullable() postgresql.org/docs/current/static/…

Collectives™ on Stack Overflow

Postgresql format() function alternative

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related