postgres accessing field in RECORD type variable by text variables

Question

I have a function in Postgresql and I want to select from a table and insert into another table.


CREATE OR REPLACE FUNCTION "public"."update_table"("table_name" varchar, "key_col" varchar, "title_col" varchar)
  RETURNS "pg_catalog"."bool" AS $BODY$
    DECLARE
        temprow record;
    BEGIN

    FOR temprow IN
        EXECUTE 'SELECT '|| table_key ||', '|| table_title ||' FROM '|| table_name
    LOOP
        EXECUTE 'INSERT INTO coding(title, code,"parent_code") VALUES ('|| temprow.title_col ||', '|| temprow.key_col ||', 2);';
    END LOOP;

    RETURN 't';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

there is an error for accessing temprow value with key_col variable , how can I access to this fields?

ERROR:  record "temprow" has no field "table_title"

user330315 · Accepted Answer · 2019-06-25 08:14:52Z

1

You don't need a loop or a temporary record variable, just use an INSERT ... SELECT

Additionally: when you are dealing with dynamic SQL, you should always use the format() function and %I placeholders for table and column names to properly deal with quoting.

CREATE OR REPLACE FUNCTION "public"."update_table"("table_name" varchar, "key_col" varchar, "title_col" varchar)
  RETURNS "pg_catalog"."bool" AS $BODY$
BEGIN

    EXECUTE format('INSERT INTO coding (title, code, "parent_code") 
                    SELECT %I, %I, 2 
                    FROM %I', table_key, table_title, table_name'); 

    RETURN 't';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

answered Jun 25, 2019 at 8:14

user330315

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

3 Comments

vatandoost Over a year ago

I need to insert into coding table for each row of first select so I need a loop to getting data from table_name and inserting to coding table

user330315 Over a year ago

No, you do not need a loop - the SELECT will retrieve all rows from the table and all those rows will be inserted into the coding table. A loop is definitely not needed here.

vatandoost Over a year ago

Ok I got it and your answer is true but if I want to access to fields of record with another text variable , how can i do that?

Collectives™ on Stack Overflow

postgres accessing field in RECORD type variable by text variables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related