SQL – omit repeating the table name

December 15, 2021

Let’s say I want to create a new table from an existing table in SQL (postgres). I want the new table to have the same name as the old table but I want it to be in a different schema.

Is there a way to do this without having to repeat the name of the two tables (who share one name?)

Let’s say the name of the original table is public.student

CREATE TABLE student(
    student_id INT PRIMARY KEY,
    last_name VARCHAR(30),
    major VARCHAR(30))

Now I want to have the exact table but I want it to be in test.student
I know I would "clone" that table via

CREATE TABLE test.student AS 
SELECT * 
FROM public.student;

but I would like to write this without having to repeat writing "student".
Is there a way to write a function for this?

I’m quite new to SQL, so I’m thankful for any help! I looked into functions and I wasn’t able to make it work.

>Solution :

You could create a procedure (or a function) with dynamic SQL:

CREATE OR REPLACE PROCEDURE foo(_schema text, _table text)
    LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('CREATE TABLE %1$I.%2$I AS TABLE public.%2$I'
                , _schema, _table);
END
$func$;

Call:

CALL foo('test', 'student');

Note that identifers are case sensitive here!

Be wary of possible SQL injection. format() with the format specifier %I (for identifier) is safe. (nested $1, $2 are ordinal references to format input)

See: