SQL - omit repeating the table name

Question

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.

There is not. Otherwise the database would have no idea what table is being used to create the other. — Adrian Klaver, Dec 15 '21 at 18:21
Define "repeat". You mean, you want to type it only *once*? And always your version of Postgres. — Erwin Brandstetter, Dec 15 '21 at 18:22
yes, I only want to write "student" once (the name is a lot more complex in reality). I use the latest version which is 14.1 — evam, Dec 15 '21 at 18:24

score 2 · Accepted Answer · answered Dec 15 '21 at 18:31

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:

SQL - omit repeating the table name

1 Answers1