Generate dynamic JSON in PostgreSQL 9.5

Question

I'd like to generate queries dynamically in Postgres 9.5.

This is my table :

    | id |  ObjectType |            content            |
    -----+-------------+--------------------------------
    |  1 |      test   |  {"test_name": "test object"} |
    ----------------------------------------------------

This is my query

DO $$
DECLARE name text;
DECLARE label text;
BEGIN

select 'test' into name;
select 'test object' into label;
insert into "Constantes" (objecttype,content) values ('test','{"test_name":"test object"}')

END $$;

I'l like, in the insert query, to replace 'test' by the var 'name' and replace 'test_name' by

name+ '_name

And finally replace 'test object' by the label variable.

I didn't manage to, I tried to use + operator, || operator but nothing works.

My goal is only to change 'name' and 'label' variables because I need to execute this query a lot of times with different values.

Mahesh H Viraktamath · Accepted Answer · 2017-09-28 05:57:08Z

1

I hope you will be using this block inside a function and call that function by passing values ('name' --> 'label') to that. If that is so, you can use numbered parameters. Check the official postgresql documentation it also has some examples that you could use - https://www.postgresql.org/docs/9.5/static/xfunc-sql.html

CREATE OR REPLACE FUNCTION insertRow(text, text) returns void AS $$
DECLARE
    name ALIAS FOR $1;
    label ALIAS FOR $2;
BEGIN
    insert into Constantes (objecttype,content) VALUES (name, '{"' || name || '_name":"' || label || '"}');
END;
$$ LANGUAGE plpgsql;

edited Sep 28, 2017 at 5:57

answered Sep 27, 2017 at 14:11

Mahesh H Viraktamath

8943 gold badges17 silver badges42 bronze badges

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

2 Comments

galiolio Over a year ago

there is no other possibility? juste by declaring two variables, affect a value and putting them in the query?

Mahesh H Viraktamath Over a year ago

I have edited the answer with a code sample, hope it helps.

Collectives™ on Stack Overflow

Generate dynamic JSON in PostgreSQL 9.5

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related