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 ?