Appending character to variable in execute query

Question

I want to execute a dynamic SQL statement, which searches for names whose last name is always a constant and first name is a variable. Here is a query I have written for selecting a row with name='Test lastname'.

EXECUTE 'SELECT name FROM users 
        WHERE name=$1 lastname'
USING ('Test');

This generates a syntax error. Is it possible to do this?

to debug: store your sql to a variable and write out the variable so you can see what was built. — xQbert
– xQbert, Commented Jun 5, 2017 at 13:12

Oto Shavadze · Accepted Answer · 2017-06-05 14:09:35Z

1

I think you need something like this:

EXECUTE 'SELECT user_id FROM users 
        WHERE name=$1'
USING  Test||' lastname' ;

Here Test is variable and 'lastname' is hard coded value

Also another way is as @JorgeCampos mentioned:

...WHERE name=$1 || '' lastname''' USING 'Test';

edited Jun 5, 2017 at 14:09

answered Jun 5, 2017 at 13:17

Oto Shavadze

43.3k56 gold badges169 silver badges248 bronze badges

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

12 Comments

Jorge Campos Over a year ago

Almost... since lastname seems to be a column from the users table I think he wants: ...WHERE name=$1 || lastname' USING 'Test '

Oto Shavadze Over a year ago

@JorgeCampos - As I understand, he have only name column and stores both first and last names there

jabirm9231 Over a year ago

I want rows with name = 'Test lastname' where the last name is always 'lastname' and first name is a variable.

Jorge Campos Over a year ago

There is no difference as of now. By the time he asked your answer was still with the 'Test ' || lastname variation (I think). And since you provide the right way (even though slightly different) I choose just to collaborate with your answer :) cheers

Oto Shavadze Over a year ago

@JorgeCampos - Ah, I understand now, your comment was before... Thank you again :)

|

Collectives™ on Stack Overflow

Appending character to variable in execute query

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related