pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to export query results
4+ messages / 2 participants
[nested] [flat]

* How to export query results
@ 2007-08-21 14:54 Chris Hoover <revoohc@gmail.com>
  2007-08-21 15:33 ` Re: How to export query results Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Chris Hoover @ 2007-08-21 14:54 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to export query results
  2007-08-21 14:54 How to export query results Chris Hoover <revoohc@gmail.com>
@ 2007-08-21 15:33 ` Scott Marlowe <scott.marlowe@gmail.com>
  2007-08-21 16:09   ` Re: How to export query results Chris Hoover <revoohc@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Scott Marlowe @ 2007-08-21 15:33 UTC (permalink / raw)
  To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin

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.



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to export query results
  2007-08-21 14:54 How to export query results Chris Hoover <revoohc@gmail.com>
  2007-08-21 15:33 ` Re: How to export query results Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-08-21 16:09   ` Chris Hoover <revoohc@gmail.com>
  2007-08-21 16:21     ` Re: How to export query results Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Chris Hoover @ 2007-08-21 16:09 UTC (permalink / raw)
  To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin

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)?

Thanks,

Chris

PG 8.1

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to export query results
  2007-08-21 14:54 How to export query results Chris Hoover <revoohc@gmail.com>
  2007-08-21 15:33 ` Re: How to export query results Scott Marlowe <scott.marlowe@gmail.com>
  2007-08-21 16:09   ` Re: How to export query results Chris Hoover <revoohc@gmail.com>
@ 2007-08-21 16:21     ` Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Scott Marlowe @ 2007-08-21 16:21 UTC (permalink / raw)
  To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin

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.



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2007-08-21 16:21 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-21 14:54 How to export query results Chris Hoover <revoohc@gmail.com>
2007-08-21 15:33 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-08-21 16:09   ` Chris Hoover <revoohc@gmail.com>
2007-08-21 16:21     ` Scott Marlowe <scott.marlowe@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox