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
– Laurenz Albe, Commented Apr 7, 2020 at 7:24
@LaurenzAlbe Please check my edited question where i add #!/bin/sh in question. — ankit
– ankit, Commented Apr 7, 2020 at 8: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
– Laurenz Albe, Commented Apr 7, 2020 at 8:37
@LaurenzAlbe I am new in scripting. If you can provide my concept in PL/Python, then please provide me. — ankit
– ankit, Commented Apr 7, 2020 at 8:47
Then your PostgreSQL was built without Python support. Trust me, you don't want to solve this in C. — Laurenz Albe
– Laurenz Albe, Commented Apr 7, 2020 at 13:19

Laurenz Albe · Accepted Answer · 2020-04-09 06:19:09Z

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 9, 2020 at 6:19

answered Apr 7, 2020 at 13:22

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ankit Over a year ago

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.

Laurenz Albe Over a year ago

Right. You don't need the PROGRAM option.

Collectives™ on Stack Overflow

Running system command in Postgres and use return value in Insert

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related