Skip to main content
Solved

How to return value from Postgres procedure or function to SQL executor's Result port?

  • October 20, 2021
  • 2 replies
  • 715 views

f.kemminje
Contributor
Forum|alt.badge.img+11

safeoct21I am renaming the Postgres tables inside the SQL executor(Postgres).

If the task is successful, I need a success mark or flag from the SQL executor(stored proc).

here there are two flags variables in my code ie "done2" and "i"

But the initiator only comes out, nothing comes from the output port.

 

I need one feature from Result port 1 for success or 0 for failure.

Can Anyone help me?

CREATE OR REPLACE FUNCTION func1()
  RETURNS integer AS
$BODY$
DECLARE
   cntold INTEGER := 0 ; 
   cntcur INTEGER := 0;
   i INTEGER := 0;
   BEGIN
        SELECT INTO cntold count(*) FROM @Value(table_name_old) limit 10;
        SELECT INTO cntcur count(*) FROM @Value(table_name) limit 10;
        --SELECT INTO cntnew count(*) FROM @Value(table_name_new) limit 10;
        IF cntold > 0 and cntcur > 0 THEN
            EXECUTE 'DROP TABLE @Value(table_name_old)';
            EXECUTE 'ALTER TABLE @Value(table_name) RENAME TO @Value(table_name_old)';
            EXECUTE 'ALTER TABLE @Value(table_name_new) RENAME TO @Value(table_name)';
            EXECUTE 'SELECT 1 AS DONE2';
            i=1;
            return i;
        END IF;
    END; $BODY$
    LANGUAGE plpgsql VOLATILE

 

 

 

Best answer by david_r

I haven't tested it, but I suspect that your function has to return something that is table-like (rather than an integer) for FME to pick it up. 

First google hit looks promising: https://www.postgresqltutorial.com/plpgsql-function-returns-a-table/

In your SQLExecutor you can then try 

select * from func1()

I'm curious to hear how that works.

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

2 replies

david_r
Celebrity
  • 8391 replies
  • Best Answer
  • October 21, 2021

I haven't tested it, but I suspect that your function has to return something that is table-like (rather than an integer) for FME to pick it up. 

First google hit looks promising: https://www.postgresqltutorial.com/plpgsql-function-returns-a-table/

In your SQLExecutor you can then try 

select * from func1()

I'm curious to hear how that works.


f.kemminje
Contributor
Forum|alt.badge.img+11
  • Author
  • Contributor
  • 189 replies
  • October 21, 2021

I haven't tested it, but I suspect that your function has to return something that is table-like (rather than an integer) for FME to pick it up. 

First google hit looks promising: https://www.postgresqltutorial.com/plpgsql-function-returns-a-table/

In your SQLExecutor you can then try 

select * from func1()

I'm curious to hear how that works.

@david_r​ I added One more SQL executor after the first SQL executor (create function), and pasted your string "select * from func1()". It worked David.

Thank you very much.