Using variables inside a string in plpgsql

Question

I'm trying to build a regex match with variables as part of the matched string:

var_a := 'somestring'
var_b := 'someotherstring'

IF EXISTS (SELECT 1 FROM some_table WHERE some_field ~* 'var_a.+?(?=\-)\-var_b)')

How do I insert the variables into the string? Like in js you can simply do:

`${var_a} restofstring`

user330315 · Accepted Answer · 2019-10-17 10:33:40Z

1

You could use the format() function:

WHERE some_field ~* format('%s.+?(?=\-)\-%s)', var_a, var_b)

answered Oct 17, 2019 at 10:33

user330315

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

4 Comments

S. Schenk Over a year ago

I've just managed to do the same thing (before reading your answer) like so: ~*(var_a||'.+?'||var_b) It seems to work. Is it also acceptable or should I use the format function with % for the vars?

user330315 Over a year ago

@S.Schenk: I find solutions using format() way easier to read and maintain compared to the equivalent using ||. But that's largely personal taste. But it becomes "mandatory" if it's used to generate dynamic SQL.

S. Schenk Over a year ago

It's mandatory when used for dynamic sql to prevent sql injections right?

user330315 Over a year ago

That's one of the reasons, yes. I wrote "mandatory" in the sense that it's the only sane option where the base SQL is still readable.

Collectives™ on Stack Overflow

Using variables inside a string in plpgsql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related