Thread: list all members in a tablespace

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
objectsin a tablespace. Is there a command to list all objects in a tablespace? 

Thanks,

Mike

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.

> Date: Thu, 10 Mar 2011 08:22:13 -0600
> Subject: Re: [GENERAL] 9.1 Trigger question
> From: mmoncure@gmail.com
> To: michaelblack75052@hotmail.com
> CC: pgsql-general@postgresql.org
>
> 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