Running system command in Postgres and use return value in Insert

Question

I have a linux command that i want to run in following function:

CREATE FUNCTION tarifador_func2() RETURNS TRIGGER
LANGUAGE plsh
AS $$
#!/bin/sh
SET userid[] = // here i want to run linux command and use that return value to insert into below table
for loop with i index
INSERT INTO public.act_id_priv_mapping(id_, priv_id_, user_id_, group_id_) VALUES (auuid,new.priv_id_,userid[i],new.group_id_);
$$;

I have following values return by command with new line:

ankit
ankit1

Can anyone help me to complete my function. Thanks

That does not look like shell code at all. I am not surprised that it does not work with PL/sh. What language is that supposed to be? — Laurenz Albe, Apr 07 '20 at 07:24
@LaurenzAlbe Please check my edited question where i add `#!/bin/sh` in question. — ankit, Apr 07 '20 at 08:08
But what you are writing isn't shell code at all, and that comment doesn't change that. Why don't you use PL/Python or PL/Perl and run your system command from there? — Laurenz Albe, Apr 07 '20 at 08:37
@LaurenzAlbe I am new in scripting. If you can provide my concept in PL/Python, then please provide me. — ankit, Apr 07 '20 at 08:47
@LaurenzAlbe I have 4 languages in my postgresql: plpgsql, sql, c, internal. Can i hit linux command using these languages? — ankit, Apr 07 '20 at 12:50
With C you can. Sorry, but giving you a tutorial about C or Python programming is beyond the scope of a Stackoverflow answer. — Laurenz Albe, Apr 07 '20 at 12:53
@LaurenzAlbe Atleast provide me reference link for that. I will do it by my own. Thanks — ankit, Apr 07 '20 at 13:07
@LaurenzAlbe I know python but python language is not available in my postgres:9.6-alpine. And while creating language getting error: `could not access file "$libdir/plpython2": No such file or directory` — ankit, Apr 07 '20 at 13:12
Then your PostgreSQL was built without Python support. Trust me, you don't want to solve this in C. — Laurenz Albe, Apr 07 '20 at 13:19

Laurenz Albe · Answer 1 · 2020-04-09T06:19:09.833

0

You should solve this problem differently.

Use COPY ... FROM PROGRAM to execute the shell command and populate a (temporary?) table with the result.

edited Apr 09 '20 at 06:19

answered Apr 07 '20 at 13:22

Laurenz Albe

209,280
17
206
263

Thanks for answer but i am getting error `SQL Error [HV00D]: ERROR: invalid option "program" Hint: Valid options in this context are: filename, format, header, delimiter, quote, escape, null, encoding` while creating foreign table. – ankit Apr 08 '20 at 00:58
Right. You don't need the `PROGRAM` option. – Laurenz Albe Apr 08 '20 at 06:09

Running system command in Postgres and use return value in Insert

1 Answers1

Linked