pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow 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