list all members in a tablespace

Hi Mike,

I tried this, which will get the list of tables belong to 'XYZ' tablespace.

select relname from pg_class where reltablespace=(select oid from
pg_tablespace where spcname='xyz');

Hope this helps

Best Regards,
Raghavendra
EnterpriseDB Corporation
The Enterprise Postgres Company

On Thu, Mar 10, 2011 at 6:05 AM, Michael Andrew Babb <babbm@uw.edu> wrote:

The following from 9.1 documentation on triggers ----

"SQL allows you to define aliases for the "old"
and "new" rows or tables for use in the definition
of the triggered action (e.g., CREATE TRIGGER ... ON
tablename REFERENCING OLD ROW AS somename NEW ROW AS othername
...). Since PostgreSQL
allows trigger procedures to be written in any number of
user-defined languages, access to the data is handled in a
language-specific way.
"

This seems to imply that triggers actually have to reference a function
rather than containing the actual code for the trigger to perform. For
example the only valid format of a trigger is to

CREATE TRIGGER view_insert
    --- other parameters here ---
   EXECUTE PROCEDURE view_insert_row();

Instead of the normal way

CREATE TRIGGER view_insert
    --- other parameters here ---
   AS
    --- sql functions, conditions and statements ---
;

Is my understand in of this correct? If so, how does the other language know the old record from the new?

On Wed, Mar 9, 2011 at 8:24 PM, Michael Black
<michaelblack75052@hotmail.com> wrote:

The following from 9.1 documentation on triggers ----

"SQL allows you to define aliases for the "old" and "new" rows or tables for
use in the definition of the triggered action (e.g., CREATE TRIGGER ... ON
tablename REFERENCING OLD ROW AS somename NEW ROW AS othername ...). Since
PostgreSQL allows trigger procedures to be written in any number of
user-defined languages, access to the data is handled in a language-specific
way. "

This seems to imply that triggers actually have to reference a function
rather than containing the actual code for the trigger to perform.  For
example the only valid format of a trigger is to

CREATE TRIGGER view_insert
--- other parameters here ---
EXECUTE PROCEDURE view_insert_row();

Instead of the normal way

CREATE TRIGGER view_insert
--- other parameters here ---
AS
--- sql functions, conditions and statements ---
;

Is my understand in of this correct? If so, how does the other language
know the old record from the new?

This is correct. In PostgreSQL, the trigger function is always
separate from the trigger definition. Procedures written in various
languages in PostgreSQL have to wrap an internal API that provides
access to various features, querying, etc. Take a look for example
how it is exposeed in python:
http://www.postgresql.org/docs/9.0/interactive/plpython-trigger.html
-- if you wanted to see how that was done you could look at the code
for the python language handler.

merlin

Thank you for the link Merlin.

Thanks Raghavendra. I tried the query and it seemed to be returning the indices and sequences in each tablespace. I'll keep trying to get the list of tablespaces.

Thanks for your help!

Mike

From: Raghavendra [mailto:raghavendra.rao@enterprisedb.com]
Sent: Wednesday, March 09, 2011 5:15 PM
To: Michael Andrew Babb
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] list all members in a tablespace

Hi Mike,

I tried this, which will get the list of tables belong to 'XYZ' tablespace.

select relname from pg_class where reltablespace=(select oid from pg_tablespace where spcname='xyz');

Hope this helps

Best Regards,
Raghavendra
EnterpriseDB Corporation
The Enterprise Postgres Company

On Thu, Mar 10, 2011 at 6:05 AM, Michael Andrew Babb <babbm@uw.edu<mailto:babbm@uw.edu>> wrote:
Hi All,

I'm doing a little housekeeping on my tablespaces and I'm curious if there is a quick and easy way to list all of the objects in a tablespace. Is there a command to list all objects in a tablespace?

Thanks,

Mike

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general