SEO

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

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend