Why is json_populate_record() not recognizing row type?

Question

I have 2 tables type and name. I am trying to insert into these with json_populate_record() through a function. Receiving the following error:

first argument of json_populate_record must be a row type

Table type is created using:

CREATE TABLE IF NOT EXISTS "type" (
  "id"    TEXT NOT NULL,
  "value" TEXT NOT NULL,
  PRIMARY KEY ("id"),
  CHECK ("id"<>'' AND "value"<>'')
);
CREATE INDEX IF NOT EXISTS "idx_type_value"
ON "type" ("value");


CREATE OR REPLACE FUNCTION "type_insertone" (
  IN _a JSON
) RETURNS VOID AS $$
BEGIN
  INSERT INTO "type" SELECT * FROM json_populate_record(NULL::"type", _a);
END;
$$ LANGUAGE plpgsql;

Table name is created using:

CREATE TABLE IF NOT EXISTS "name" (
  "id"    TEXT NOT NULL,
  "value" TEXT NOT NULL,
  PRIMARY KEY ("id"),
  CHECK ("id"<>'' AND "value"<>'')
);
CREATE INDEX IF NOT EXISTS "idx_name_value"
ON "name" ("value");


CREATE OR REPLACE FUNCTION "name_insertone" (
  IN _a JSON
) RETURNS VOID AS $$
BEGIN
  INSERT INTO "name" SELECT * FROM json_populate_record(NULL::"name", _a);
END;
$$ LANGUAGE plpgsql;

When is call type_insertone() with {id: 'x', 'value': 'x'}, it works. But when i do the same with name_insertone() it doesnt work!

I was thinking if it could be possible that the function is created before the table is created, which could be causing it to not know the table name, but that i think is not the case.

I dont know what should i try here. I have also checked that name is not a reserved keyword and so is type.

Answer 1

"name" is a text type and a key word:

https://www.postgresql.org/docs/current/static/datatype-character.html look at the bottom, so when you cast to "name" you cast to name data type, not to your table rowtype...

eg:

t=# alter table name rename to name1;
ALTER TABLE
t=# CREATE OR REPLACE FUNCTION "name_insertone" (
  IN _a JSON
) RETURNS VOID AS $$
BEGIN
  INSERT INTO "name1" SELECT * FROM json_populate_record(NULL::"name1", _a);
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
t=# select name_insertone('{"id": "x", "value": "x"}');
 name_insertone
----------------

(1 row)

and yet:

t=# create table text(i int);
CREATE TABLE
t=# SELECT * FROM json_populate_record(NULL::"text", '{}')
t-# ;
ERROR:  first argument of json_populate_record must be a row type
t=# create table char(i int);
CREATE TABLE
t=# SELECT * FROM json_populate_record(NULL::"char", '{}');
ERROR:  first argument of json_populate_record must be a row type