CODEWITHSUNDEEP
sqlpostgresqlplpgsqldynamic-sqlpostgresql-8.3
jzadeh
jzadeh

Reputation: 703

Parametrizing a table name

I am working on a Postgres 8.3 database and was trying to use PL/pgSQL to create function that generates a table name based on dates input to the table:

I have to create a series of tables that are indexed by dates for example:

drop table one_day_11_13 cascade;
create table one_day_11_13 as
select * from master_table where
timestamp < '2012-11-14' and timestamp >= '2012-11-13';  

drop table one_day_11_14 cascade;
create table one_day_11_14 as
select * from master_table where
timestamp < '2012-11-15' and timestamp >= '2012-11-14';

I was thinking the way to do this would be to create a PL/pgSQL function that took as arguments ('11_13', '2012-11-14', '2012-11-13') for example to create the first table above. I am having trouble coding up the appropriate EXECUTE statements.

What is the best way to do this kind of thing is PL/pgSQL overkill?

Upvotes: 1

Views: 300

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

First, consider upgrading to a more recent version. 8.3 is on its way out.

For 8.3 it could work like this:

CREATE OR REPLACE FUNCTION f_recreate_tbl(date, date)
 RETURNS void AS
$func$
DECLARE
   -- derive tablename from first date
   _tbl text := 'one_day_' || to_char($1, 'MM_YY'); 
BEGIN

 -- Use IF EXISTS to avoid an exception when table does not exist.
EXECUTE
'DROP TABLE IF EXISTS ' || _tbl || ' CASCADE';

EXECUTE 
'CREATE TABLE ' || _tbl || ' AS
SELECT *
FROM   master_table
WHERE  timestamp >= ''' || $1::text || '''::date
AND    timestamp <  ''' || $2::text || '''::date';

END
$func$ LANGUAGE plpgsql;

Call:

SELECT f_recreate_tbl('2012-11-13'::date, '2012-11-14'::date);

  • Whenever you want to parameterize identifiers, you can't use pure SQL. Use EXECUTE dynamic SQL in a PL/pgSQL function or DO statement (version 9.0+).

  • This version is safe enough. With more flexible input you would have to sanitize identifiers and values with quote_ident() and quote_literal() to prevent possible SQL injection.

  • In modern versions (since 8.4) you would use the USING clause to skip casting parameters to text.

  • Don't use timestamp as column name. It's the name of a data type.

  • Use DROP TABLE IF EXISTS. Already available in 8.3.

  • I derive the table name from the date and skip the third parameter. Seemed like a simplification.

Upvotes: 4

Related Questions