CODEWITHSUNDEEP
postgresqlplpgsql
JessePinkman
JessePinkman

Reputation: 651

Postgres: How to insert double quotes in a string in a psql query?

I have this query which works perfectly fine. Here, "data" is a jsonb column in "info" table.

select data from info where data @> '{"last_name": "Pinkman"}';

Now, I have to pass this particular query in a function as a parameter. Inside the function, I am trying to form a jsonb object by concatenating this query in the following manner but I am getting an error.

CREATE OR REPLACE FUNCTION run_query(query text) RETURNS jsonb AS $$
    DECLARE
        obj text;
    BEGIN
        obj := '{' || '"query"' || ':"' || query || '"}';
        RETURN obj::jsonb;
    END
$$ LANGUAGE plpgsql;

db=# select run_query('select data from info where data @> ''{"last_name": "Pinkman"}''');
ERROR:  invalid input syntax for type json
DETAIL:  Token "last_name" is invalid.
CONTEXT:  JSON data, line 1: ..."select data from info where data @> '{"last_name...
PL/pgSQL function run_query(text) line 6 at RETURN

I know I can use jsonb_build_object() to create the object but I have to create it as a string first and then cast into jsonb. How do I do that? Thx.

Upvotes: 0

Views: 839

Answers (1)

user330315
user330315

Reputation:

I know I can use jsonb_build_object() to create the object but I have to create it as a string first

Then do so:

CREATE OR REPLACE FUNCTION run_query(query text) 
  RETURNS jsonb
AS
$$
DECLARE
  obj text;
BEGIN
  obj := jsonb_build_object('query', query)::text;

  ... work with the text ...

  RETURN obj::jsonb; 
END
$$
LANGUAGE plpgsql;

Upvotes: 1

Related Questions