Generating TRUNCATE orders
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 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 !
Have fun,
L@u
The Computing Froggy
_____________________________________________________________________________
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
Laurent ROCHE wrote:
So I wrote:
SELECT 'TRUNCATE TABLE '
UNION
...
ORDER BY relname
And this fails with the following message:
ERROR: column "relname" does not exist
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.
The "ORDER BY" is attached to the "UNION" not the second subquery.
Catches everyone out from time to time.
--
Richard Huxton
Archonet Ltd
On 10/3/07, Laurent ROCHE <laurent_roche@yahoo.com> wrote:
Would this work:
SELECT
'TRUNCATE TABLE ' ||
'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
On Oct 3, 2007, at 12:19 PM, Scott Marlowe wrote:
On 10/3/07, Laurent ROCHE <laurent_roche@yahoo.com> wrote:
Would this work:
SELECT
'TRUNCATE TABLE ' ||
'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
Or, just:
SELECT 'TRUNCATE ' || schemaname || '.' || tablename ';'
FROM pg_tables
WHERE schemname='my_schema'
ORDER BY tablename;
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
Erik Jones wrote:
On Oct 3, 2007, at 12:19 PM, Scott Marlowe wrote:
SELECT 'TRUNCATE ' || schemaname || '.' || tablename ';'
FROM pg_tables
WHERE schemname='my_schema'
ORDER BY tablename;
To be safe, you'd probably want to write
SELECT 'TRUNCATE' || quote_ident(schemaname) || '.' || quote_ident(tablename) ||
';' ...
Otherwise, table or schema names containing funny characters, upper case, or
spaces will cause trouble..
greetings, Florian Pflug