CODEWITHSUNDEEP
postgresql
zcaudate
zcaudate

Reputation: 14258

best way to turn a json entry into a table record

I have a table:

CREATE TABLE IF NOT EXISTS "account" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "nickname" TEXT NOT NULL UNIQUE,
  "time_updated" TIMESTAMPTZ NOT NULL
);

and a function:

CREATE OR REPLACE FUNCTION account_register(
  e JSON,
  t TIMESTAMPTZ DEFAULT now()
) RETURNS JSON AS $$
DECLARE
  o_account ACCOUNT;
BEGIN
  INSERT INTO "account" (nickname, time_updated) VALUES (e -> 'nickname', t) RETURNING * INTO o_account;
  RETURN row_to_json(o_account);
END
$$ LANGUAGE 'plpgsql';

It works like

SELECT account_register('{"nickname":"bob"}'); 

--
-- {"id"":"d3a771a9-fcff-419f-ad80-b8f5caa847da",
--  "nickname":"bob",
--  "time_updated":"2021-02-08T00:32:25.698293"}
--

I'd like to be able to replace the e JSON to acc ACCOUNT and have it looking something like this:

CREATE OR REPLACE FUNCTION account_register_new(
  acc ACCOUNT, -- was JSON
  t TIMESTAMPTZ DEFAULT now()
) RETURNS ACCOUNT AS $$ -- was JSON
DECLARE
  o_account ACCOUNT;
BEGIN
  -- <2. NEEDS HELP>
  INSERT INTO "account" <RECORD acc> RETURNING * INTO o_account;
  RETURN o_account
END
$$ LANGUAGE 'plpgsql';

to be used like:

-- <1. NEEDS HELP>
SELECT account_register(<TO_ACCOUNT>('{"nickname":"bob"}'));

So I have two questions:

  1. How to convert a json {"nickname":"bob"} into a record of type ACCOUNT
  2. How to insert a record of type ACCOUNT into the ACCOUNT table

Upvotes: 0

Views: 47

Answers (1)

user330315
user330315

Reputation:

You need to access the columns from the passed record in the function:

CREATE OR REPLACE FUNCTION account_register(
  p_new_row account, 
  p_time_updated TIMESTAMPTZ DEFAULT now()
) 
  RETURNS account
AS $$
  insert into account (nickname, time_updated)
  values ((p_new_row).nickname, p_time_updated)
  returning *;
$$ 
LANGUAGE sql;

To convert a JSON value to a known record type, use json_populate_record()

select account_register(json_populate_record (null::account, '{"nickname":"bob"}'));

Btw: your function which accepts a JSON value can be simplified. There is no need to use PL/pgSQL. And you should extract the nickname using ->> so you get a text value, rather than json (or jsonb).

CREATE OR REPLACE FUNCTION account_register(
  p_new_row jsonb, 
  p_time_updated  TIMESTAMPTZ DEFAULT now()
) 
  RETURNS jsonb 
AS $$
  with new_account as (
    INSERT INTO "account" (nickname, time_updated) 
    VALUES (e ->> 'nickname', t) 
    RETURNING *
  )
  select to_jsonb(new_account)
  from new_account;
$$ 
LANGUAGE sql;

jsonb is the recommended data type to work with JSON in Postgres.

Upvotes: 1

Related Questions