Postgresql, select a "fake" row

Question

In Postgres 8.4 or higher, what is the most efficient way to get a row of data populated by defaults without actually creating the row. Eg, as a transaction (pseudocode):

create table "mytable"
(
  id serial PRIMARY KEY NOT NULL,
  parent_id integer NOT NULL DEFAULT 1,
  random_id integer NOT NULL DEFAULT random(),
)

begin transaction
  fake_row = insert into mytable (id) values (0) returning *;
  delete from mytable where id=0;
  return fake_row;
end transaction

Basically I'd expect a query with a single row where parent_id is 1 and random_id is a random number (or other function return value) but I don't want this record to persist in the table or impact on the primary key sequence serial_id_seq.

My options seem to be using a transaction like above or creating views which are copies of the table with the fake row added but I don't know all the pros and cons of each or whether a better way exists.

I'm looking for an answer that assumes no prior knowledge of the datatypes or default values of any column except id or the number or ordering of the columns. Only the table name will be known and that a record with id 0 should not exist in the table.

In the past I created the fake record 0 as a permanent record but I've come to consider this record a type of pollution (since I typically have to filter it out of future queries).

Answer 1

You can copy the table definition and defaults to the temp table with:

CREATE TEMP TABLE table_name_rt (LIKE table_name INCLUDING DEFAULTS);

And use this temp table to generate dummy rows. Such table will be dropped at the end of the session (or transaction) and will only be visible to current session.

Answer 2

You can query the catalog and build a dynamic query

Say we have this table:

create table test10(
      id serial primary key,
      first_name varchar( 100 ),
      last_name  varchar( 100 ) default 'Tom',
      age int not null default 38,
      salary float  default 100.22
);

When you run following query:

SELECT string_agg( txt, ' ' order by id ) 
FROM (
select 1 id, 'SELECT ' txt
union all
select 2, -9999 || ' as id '
union all
select 3, ', '  
       || coalesce( column_default,  'null'||'::'||c.data_type ) 
       || ' as ' || c.column_name
from information_schema.columns c
where table_schema = 'public'
    and table_name = 'test10'
    and ordinal_position > 1
) xx
    ;

you will get this sting as a result:

"SELECT  -9999 as id  , null::character varying as first_name , 
'Tom'::character varying as last_name , 38 as age , 100.22 as salary"

then execute this query and you will get the "phantom row".

We can build a function that build and excecutes the query and return our row as a result:

CREATE OR REPLACE FUNCTION get_phantom_rec (p_i test10.id%type ) 
returns test10 as $$
DECLARE 
    v_sql text;
    myrow test10%rowtype;
begin
   SELECT string_agg( txt, ' ' order by id ) 
   INTO v_sql
   FROM (
    select 1 id, 'SELECT ' txt
    union all
    select 2, p_i || ' as id '
    union all
    select 3, ', '  
           || coalesce( column_default,  'null'||'::'||c.data_type ) 
           || ' as ' || c.column_name
    from information_schema.columns c
    where table_schema = 'public'
        and table_name = 'test10'
        and ordinal_position > 1
    ) xx
    ;
    EXECUTE v_sql INTO myrow;
    RETURN  myrow;
END$$ LANGUAGE plpgsql ;

and then this simple query gives you what you want:

select * from get_phantom_rec ( -9999 );

  id   | first_name | last_name | age | salary
-------+------------+-----------+-----+--------
 -9999 |            | Tom       |  38 | 100.22

Answer 3

I would just select the fake values as literals:

select 1 id, 1 parent_id, 1 user_id

The returned row will be (virtually) indistinguishable from a real row.

---

To get the values from the catalog:

select
  0 as id, -- special case for serial type, just return 0
  (select column_default::int -- Cast to int, because we know the column is int
   from INFORMATION_SCHEMA.COLUMNS
   where table_name = 'mytable'
   and column_name = 'parent_id') as parent_id,
  (select column_default::int -- Cast to int, because we know the column is int
   from INFORMATION_SCHEMA.COLUMNS
   where table_name = 'mytable'
   and column_name = 'user_id') as user_id;

Note that you must know what the columns are and their type, but this is reasonable. If you change the table schema (except default value), you would need to tweak the query.

See the above as a SQLFiddle.