Hi,
I wanted to write a SELECT that generates a TRUNCATE TABLE for all the tables in a given schema.
So I wrote:
SELECT 'TRUNCATE TABLE '
UNION
SELECT 'my_schema.' || c.relname ||', '
FROM pg_namespace nc, pg_class c
WHERE c.relnamespace = nc.oid
AND c.relkind IN ('r' )
AND nc.nspname = 'my_schema'
ORDER BY relname
UNION
SELECT 'my_schema.' || c.relname ||', '
FROM pg_namespace nc, pg_class c
WHERE c.relnamespace = nc.oid
AND c.relkind IN ('r' )
AND nc.nspname = 'my_schema'
ORDER BY relname
And this fails with the following message:
ERROR: column "relname" does not existSQL state:42703
If I run only the SELECT after the UNION that works as expected.
Of course, this is not a big deal as I copying and pasting this into a script file any way (and I will add the TRUNCATE TABLE manually).
But I don't understand why this does not work: the 2 SELECTs produce a single char column so from what I understand that should work ! ? !
If some body can explain I will be grateful.
PS: Of course, I realise the code produced by the SELECTs and UNION would not work straight away, because of the trailing comma !
L@u
The Computing Froggy
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
