1

Is it possible to create a table from a record? For example:

DO $$
DECLARE
  v1 RECORD;
BEGIN
  SELECT 1 AS c INTO v1;
  CREATE TEMP TABLE temp AS
    SELECT v1.*;
END;
$$
LANGUAGE 'plpgsql';

This returns

ERROR:  record type has not been registered
CONTEXT:  SQL statement "CREATE TEMP TABLE temp AS
    SELECT v1.*"
PL/pgSQL function inline_code_block line 6 at SQL statement

I tried more complex solutions such as using hstore to loop through the record keys and create a custom command but then I would not be able to use the same data types.

0

v1 is untyped and Postgres doesn't know what to do with that. At some point, either while defining the record or when returning it you have to specify the columns. Try specifying the column names in the SELECT within an IF for different types. I put in dummy checks for the IF to be replaced with what you need to decide the row type.

DO
$body$
DECLARE
v1 RECORD;
BEGIN

IF (1=3) THEN--logic to decide which data here
    SELECT 1 AS c1,2 AS c2,3 AS c3 INTO v1;
    CREATE TABLE temp AS SELECT v1.c1, v1.c2, v1.c3; 
ELSE
 IF (2=2) THEN--logic to decide which data here
    SELECT 4 AS c1,5 AS c2 INTO v1;
    CREATE TABLE temp AS SELECT v1.c1, v1.c2; 
 END IF;
END IF;

END;
$body$
LANGUAGE
'plpgsql'

Alternatively you could also:

DO
$body$
BEGIN

IF (1=3) THEN--logic to decide which data here
    CREATE TABLE temp AS SELECT 1 AS c1,2 AS c2,3 AS c3; 
ELSE
 IF (2=2) THEN--logic to decide which data here
    CREATE TABLE temp AS SELECT 4 AS c1,5 AS c2; 
 END IF;
END IF;

END;
$body$
LANGUAGE
'plpgsql'
  • Hi Indiri, right. But I was aiming for a solution that doesn't require to manually specify the columns as my DO block will be handling several records that have different formats. – John Sep 21 '17 at 22:22
  • I updated the answer with a way to have different types. The problem is that you do have to tell Postgres somewhere what format it needs to create, so the columns have to be specified. But this will let you have as many options as you want. – indiri Sep 22 '17 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.