CODEWITHSUNDEEP
postgresqlpostgresql-9.4
FacundoGFlores
FacundoGFlores

Reputation: 8108

Executing a query with string inside a function

I have to execute the following query inside a function:

SELECT year FROM tbl_people WHERE personal_card = 'A5B123';

It returns an integer.

The problem is, I have to do the following:

EXECUTE 'SELECT year FROM tbl_people WHERE personal_card = 'A5B123'';

But it truncates the query to:

SELECT year FROM tbl_people WHERE personal_card

I tried:

EXECUTE "SELECT year FROM tbl_people WHERE personal_card = 'A5B123'";

But also fails.

How can I achieve it?

Upvotes: 0

Views: 1541

Answers (2)

Roberto Damian Alfonso
Roberto Damian Alfonso

Reputation: 638

You can use quote_literal function like this:

EXECUTE 'SELECT year FROM tbl_people WHERE personal_card = ' || quote_literal('A5B123');

Upvotes: 2

user330315
user330315

Reputation:

Single quotes are escaped by doubling them in SQL:

EXECUTE 'SELECT year FROM tbl_people WHERE personal_card = ''A5B123''';

But a more convenient way to do this kind of thing in Postgres is to use dollar quoted strings:

EXECUTE $$SELECT year FROM tbl_people WHERE personal_card = 'A5B123'$$;

Upvotes: 3

Related Questions