Thread: 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
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
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
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.