Correct syntax for using regex with variable in PostgreSQL

Question

PostgreSQL 9.5.4

I have the below function where I am attempting to use the parameters within the Regex. Something like

CREATE OR REPLACE FUNCTION test(lastname text, firstname text, birthdate date)
  RETURNS SETOF view_patient AS
$BODY$ 

   select * from testing t
   where t.lastname ~*  '^' || $1 || ''
   order by t.lastname

$BODY$
  LANGUAGE sql VOLATILE;

The returned error is:

ERROR: argument of WHERE must be type boolean, not type text LINE 55: where t.lastname ~* '^' || $1 || ''

How is this done?

TIA

score 6 · Accepted Answer · 2017-01-13 07:04:07Z

6

You need to put the concatenation between parentheses (and you can remove the empty string at the end:

where t.lastname ~*  ('^' || $1)

alternatively:

where t.lastname ~*  concat('^', $1)

edited Jan 13, 2017 at 7:04

answered Jan 13, 2017 at 6:58

user330315

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

Collectives™ on Stack Overflow

Correct syntax for using regex with variable in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related