Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.

I am trying to use Jsonb in a postgres function. I am not able to send parameters properly.

mac=# CREATE TABLE json_test (id serial, json jsonb);
CREATE TABLE
mac=# INSERT INTO json_test (json) VALUES ('{"key": "value"}');
INSERT 0 1
mac=# SELECT * FROM json_test;
 id |       json       
----+------------------
  1 | {"key": "value"}
(1 row)

mac=# SELECT * FROM json_test WHERE json->'key' @> '"value"';
 id |       json       
----+------------------
  1 | {"key": "value"}
(1 row)

mac=# CREATE OR REPLACE FUNCTION testando() RETURNS setof int AS $$
mac$#   SELECT id FROM json_test WHERE json->'key' @> '"value"';
mac$# $$ LANGUAGE SQL;
CREATE FUNCTION
mac=# SELECT * FROM testando();
 testando 
----------
    1
(1 row)

mac=# CREATE OR REPLACE FUNCTION testando(value_param varchar) RETURNS setof int AS $$
mac$#   SELECT id FROM json_test WHERE json->'key' @> '"$1"';
mac$# $$ LANGUAGE SQL;
CREATE FUNCTION
mac=# SELECT * FROM testando('value');
 testando 
----------
(0 rows)

This query should return value:

SELECT * FROM testando('value');

Does anyone know how to send a parameter properly in this case ?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

When writing json->'key' @> '"$1"'; you are using '"$1"' as a literal constant with the value $1. Don't wrap your parameter in quotes so it actually references the parameter:

CREATE OR REPLACE FUNCTION testando(value_param varchar) RETURNS setof int AS $$
SELECT id FROM json_test WHERE json->'key' @> $1::jsonb;
$$ LANGUAGE SQL;
SELECT * FROM testando('"value"');
share|improve this answer

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.