current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

I have seen a similar post here but my situation is slightly different from anything I've found so far. I am trying to call a postgres function with parameters that I can leverage in the function logic as they pertain to the jsonb query. Here is an example of the query I'm trying to recreate with parameters.

SELECT * 
  from edit_data 
  where ( "json_field"#>'{Attributes}' )::jsonb @> 
                    '{"issue_description":"**my description**",  
                      "reporter_email":"**[email protected]**"}'::jsonb

I can run this query just fine in PGAdmin but all my attempts thus far to run this inside a function with parameters for "my description" and "[email protected]" values have failed. Here is a simple example of the function I'm trying to create:

CREATE OR REPLACE FUNCTION get_Features(
    p1 character varying,
    p2 character varying)
  RETURNS SETOF edit_metadata AS
$BODY$ 
SELECT * from edit_metadata where ("geo_json"#>'{Attributes}' )::jsonb @> '{"issue_description":**$p1**, "reporter_email":**$p2**}'::jsonb;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;

I know that the syntax is incorrect and I've been struggling with this for a day or two. Can anyone help me understand how to best deal with these double quotes around the value and leverage a parameter here?

TIA

share|improve this question
1  
It is a String, just concatenate it like '{"issue_description":"'||yourParam||'" and so on. – Jorge Campos Mar 2 at 18:00
    
I have also tried something along these lines: CREATE OR REPLACE FUNCTION get_customentitywithserializeddatatest7( p1 character varying, p2 character varying) RETURNS SETOF edit_metadata AS $BODY$ SELECT * from edit_metadata where ( "geo_json"#>'{Attributes}' )::jsonb @> '{"issue_description":COALESCE($1, issue_description), "reporter_email":COALESCE($2, reporter_email)}'::jsonb; $BODY$ LANGUAGE sql VOLATILE COST 100 ROWS 1000; – DenverDevGuy Mar 2 at 18:09
1  
You still don't get it, this '{"issue_description":"**my description**", "reporter_email":"**[email protected]**"}' is a String and as so you cant use funcion, command or variables inside it and hope postgresql will understand, it is not PHP, you have to say to it that you are forming a NEW string. It should be like (for your case): '{"issue_description":"'||p1||'", "reporter_email":"'||p2||'"}' That way you are telling to postgresql that you want to "inject" your parameter inside your String – Jorge Campos Mar 2 at 18:14
    
Hey Jorge, please see the next post. – DenverDevGuy Mar 2 at 18:21
    
What post are you talking about? Another question or another comment? – Jorge Campos Mar 2 at 18:26
up vote 1 down vote accepted

You could use function json_build_object:

select json_build_object(
    'issue_description', '**my description**',
    'reporter_email', '**[email protected]**');

And you get:

                                    json_build_object                                    
-----------------------------------------------------------------------------------------
 {"issue_description" : "**my description**", "reporter_email" : "**[email protected]**"}
(1 row)

That way there's no way you will input invalid syntax (no hassle with quoting strings) and you can swap the values with parameters.

share|improve this answer
    
Thanks for your response hruske, I'm looking into this but still unclear how to implement it with my current scenario as I'm very new to Postgres and jsonb in particular. – DenverDevGuy Mar 2 at 21:28
    
json_build_object turned the key, thank you very much! – DenverDevGuy Mar 2 at 22:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.