How to declare rows literal with specific column names in pl/pgsql?-CodePudding

I want to declare an array varible bkgws for table gangwei with a subset column names col1 and col2, here is what I try:

DO $$
  DECLARE
    bkgws gangwei(col1, col2)[] := {('fo1','bar1'),('fo2','bar2')}; -- here, syntax error
BEGIN
  INSERT INTO gangwei (config_id, col1, col2) SELECT 1, col1, col2 FROM unnest(bkgws);
END;$$;

providedgangwei's all columns are id, config_id, col1, col2.

the literal ('fo1','bar1'),('fo2','bar2') is built from app layer.

CodePudding user response：

You need to create composite type before by command CREATE TYPE AS

CREATE TYPE gangwei AS (col1 varchar, col2 varchar);

DO $$
DECLARE
  bkgws gangwei[] DEFAULT '{"(foo, bar)", "(foo2, bar2)"}';
  r record;
BEGIN
  FOR r IN SELECT * FROM unnest(bkgws)
  LOOP
    RAISE NOTICE '% %', r.col1, r.col2;
  END LOOP;
END;
$$;
 
NOTICE:  foo  bar
NOTICE:  foo2  bar2
DO

CodePudding user response：

You need to specify the column names when you unpack the array:

... FROM unnest(bkgws) AS arr(col1,col2)