pl/pgsql - How to create a table from another table

Question

I want to create a table random_record that takes in the same columns as another table simulated_records; one of the columns is grade. But I keep getting this error:

ERROR: "random_record.grade" is not a known variable
LINE 45: random_record.grade = c_grade;
^

********** Error **********

ERROR: "random_record.grade" is not a known variable
SQL state: 42601
Character: 1635

FOR i IN 1..6 LOOP

    CREATE TABLE random_record AS 
    SELECT ....

    IF random_record.grade = '-' THEN

    .....

    END IF;


....

END LOOP;

I am not sure if I am properly creating the table.

Answer 1

• you created table well, but the table is not a variable, so line

  IF random_record.grade = '-' THEN 
  

  has not any sense. It is hard to identify, what you want, because using table in this context has not any value.

• creating in table in cycle has another issue - the statement CREATE TABLE will work only in first loop of cycle. Second loop has to fail, because table exists already.

It is hard help, because this code is messy - it is mixing variables, tables together, and it is not possible. Every object has own dimension, own access methods, and these mechanisms are different.