Обсуждение: How to export query results

I need some guidance. 

I need to have an after insert trigger execute on a specific table and export the results of query to a file on the database file system.  Is this possible to do from a trigger?  If so, how can I accomplish this?  I am drawing a blank on how to export the results of the query.

Thanks,


Chris

On 8/21/07, Chris Hoover <revoohc@gmail.com> wrote:
> I need some guidance.
>
> I need to have an after insert trigger execute on a specific table and
> export the results of query to a file on the database file system.  Is this
> possible to do from a trigger?  If so, how can I accomplish this?  I am
> drawing a blank on how to export the results of the query.

Yes.  you need to use the copy command inside a function and use
security definer to say that the function runs as a superuser.

On 8/21/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:

Ok, I thought from the documentation that copy could only extract entire table or a specific column. 

If I want to extract this:

select x.name,x.create_date
from x
where x.id = new.id

how do I get copy to do this (simple example, I know)?

Thanks,

Chris

PG 8.1

On 8/21/07, Chris Hoover <revoohc@gmail.com> wrote:
> On 8/21/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> >
> > Yes.  you need to use the copy command inside a function and use
> > security definer to say that the function runs as a superuser.
> >
>
>
> Ok, I thought from the documentation that copy could only extract entire
> table or a specific column.
>
>  If I want to extract this:
>
>  select x.name,x.create_date
>  from x
>  where x.id = new.id
>
>  how do I get copy to do this (simple example, I know)?

Well, there are a couple of ways to approach this.  One is to select
it into a temp table and then copy that to a file.  Another would be
to use an untrusted pl language (plperlu etc...) to do it.