public inbox for [email protected]
help / color / mirror / Atom feedcan a linux program, running on the client side, generate data to load a temp table ?
6+ messages / 3 participants
[nested] [flat]
* can a linux program, running on the client side, generate data to load a temp table ?
@ 2026-06-19 00:51 dfgpostgres <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: dfgpostgres @ 2026-06-19 00:51 UTC (permalink / raw)
To: pgsql-general
v15.5 on linux
I've read about and tested a way for a query which calls a user function
which returns a temp table that gets its data from an external program.
Example...
CREATE OR REPLACE FUNCTION run_linux_script()
RETURNS TABLE (script_output text)
LANGUAGE plpgsql
AS $$
BEGIN
-- Create a temporary table for this session
CREATE TEMPORARY TABLE IF NOT EXISTS temp_script_results (
output_line text
);
-- Truncate in case it was used previously in the same session
TRUNCATE temp_script_results;
-- Execute the script and insert the output into the table
COPY temp_script_results (output_line)
FROM PROGRAM '/path/to/my/script/linux_script.pl';
-- Return the results to the calling user
RETURN QUERY SELECT output_line FROM temp_script_results;
-- Clean up
DROP TABLE temp_script_results;
END;
$$;
SELECT * FROM run_linux_script();
It runs, sort of, but fails because it can't find the "linux_script.pl"
script because it's looking for it on the server side.
My question is about whether or not I can get something like this to run on
the client side (where the script can be found).
Thanks in Advance :-)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can a linux program, running on the client side, generate data to load a temp table ?
@ 2026-06-19 00:56 Tom Lane <[email protected]>
parent: dfgpostgres <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2026-06-19 00:56 UTC (permalink / raw)
To: dfgpostgres <[email protected]>; +Cc: pgsql-general
dfgpostgres <[email protected]> writes:
> My question is about whether or not I can get something like this to run on
> the client side (where the script can be found).
psql's "\copy ... from program" might help you. That just addresses
the data transfer though, the other logic will need to be implemented
client-side as well.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can a linux program, running on the client side, generate data to load a temp table ?
@ 2026-06-19 01:11 Adrian Klaver <[email protected]>
parent: dfgpostgres <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2026-06-19 01:11 UTC (permalink / raw)
To: dfgpostgres <[email protected]>; pgsql-general
On 6/18/26 5:51 PM, dfgpostgres wrote:
> v15.5 on linux
>
> My question is about whether or not I can get something like this to run
> on the client side (where the script can be found).
pl/plperlu?:
https://www.postgresql.org/docs/current/plperl-trusted.html
>
> Thanks in Advance :-)
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can a linux program, running on the client side, generate data to load a temp table ?
@ 2026-06-19 14:38 dfgpostgres <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: dfgpostgres @ 2026-06-19 14:38 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; [email protected]; +Cc: pgsql-general
>> psql's "\copy ... from program" might help you.
Ya, I've used \copy for copying data in the past. But if I'm not mistaken,
the "\copy... from program" runs on the server side. If I'm wrong about
that, if there's a client side version of this, I'm all ears ! And FWIW, a
future release that has this would be great !
plperlu might work in this case. I need the "untrusted" version because I
need to dump a bytea blob out to a file, unzip it, query it (it's a SQLite
DB), use the results of the query to load a temp table. So I need to make
system calls that write to disk and run external programs. I work in a
large corp and made the request to get that up on a DB instance.
Thankfully, I already have a superuser role that might be allowed to
execute the plperlu script.
-dave
On Thu, Jun 18, 2026 at 9:11 PM Adrian Klaver <[email protected]>
wrote:
> On 6/18/26 5:51 PM, dfgpostgres wrote:
> > v15.5 on linux
> >
>
> > My question is about whether or not I can get something like this to run
> > on the client side (where the script can be found).
>
> pl/plperlu?:
>
> https://www.postgresql.org/docs/current/plperl-trusted.html
>
> >
> > Thanks in Advance :-)
> >
> >
>
>
> --
> Adrian Klaver
> [email protected]
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can a linux program, running on the client side, generate data to load a temp table ?
@ 2026-06-19 14:48 Adrian Klaver <[email protected]>
parent: dfgpostgres <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2026-06-19 14:48 UTC (permalink / raw)
To: dfgpostgres <[email protected]>; [email protected]; +Cc: pgsql-general
On 6/19/26 7:38 AM, dfgpostgres wrote:
> >> psql's "\copy ... from program" might help you.
> Ya, I've used \copy for copying data in the past. But if I'm not
> mistaken, the "\copy... from program" runs on the server side. If I'm
> wrong about that, if there's a client side version of this, I'm all
> ears ! And FWIW, a future release that has this would be great !
It is client side, but is only available in the the psql client:
https://www.postgresql.org/docs/current/app-psql.html
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: can a linux program, running on the client side, generate data to load a temp table ?
@ 2026-06-19 14:50 Adrian Klaver <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Adrian Klaver @ 2026-06-19 14:50 UTC (permalink / raw)
To: dfgpostgres <[email protected]>; [email protected]; +Cc: pgsql-general
On 6/19/26 7:48 AM, Adrian Klaver wrote:
> On 6/19/26 7:38 AM, dfgpostgres wrote:
>> >> psql's "\copy ... from program" might help you.
>> Ya, I've used \copy for copying data in the past. But if I'm not
>> mistaken, the "\copy... from program" runs on the server side. If I'm
>> wrong about that, if there's a client side version of this, I'm all
>> ears ! And FWIW, a future release that has this would be great !
>
> It is client side, but is only available in the the psql client:
>
> https://www.postgresql.org/docs/current/app-psql.html
Forgot to add to previous post:
"Performs a frontend (client) copy. This is an operation that runs an
SQL COPY command, but instead of the server reading or writing the
specified file, psql reads or writes the file and routes the data
between the server and the local file system. This means that file
accessibility and privileges are those of the local user, not the
server, and no SQL superuser privileges are required."
>
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-06-19 14:50 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-19 00:51 can a linux program, running on the client side, generate data to load a temp table ? dfgpostgres <[email protected]>
2026-06-19 00:56 ` Tom Lane <[email protected]>
2026-06-19 01:11 ` Adrian Klaver <[email protected]>
2026-06-19 14:38 ` dfgpostgres <[email protected]>
2026-06-19 14:48 ` Adrian Klaver <[email protected]>
2026-06-19 14:50 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox