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

Question

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.

@a_horse_with_no_name I have edited the question. Please have a look. Thx. — JessePinkman
– JessePinkman, Commented Jun 5, 2020 at 9:30

user330315 · Accepted Answer · 2020-06-05 09:33:46Z

1

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;

answered Jun 5, 2020 at 9:33

user330315

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

Collectives™ on Stack Overflow

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

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