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

And this fails with the following message:
ERROR: column "relname" does not exist
SQL state:42703


If I run only the SELECT after the UNION that works asexpected.
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 !

Have fun,
L@u
The Computing Froggy


__________________________________________________ ___________________________
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail