Postgres: Sending parameter to JSONB field in a function

Question

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 ?

Simo Kivistö · Accepted Answer · 2015-04-07 09:15:43Z

1

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"');

answered Apr 7, 2015 at 9:15

Simo Kivistö

4,5933 gold badges41 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

Postgres: Sending parameter to JSONB field in a function

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