0
CREATE OR REPLACE FUNCTION "public"."cross_insert"("p_name" varchar, "p_detail" varchar)
  RETURNS SETOF "pg_catalog"."varchar" AS $BODY$
BEGIN
    SELECT * FROM public.dblink(
    '
        host=10.10.10.53 
        port=5432 
        user=sassuperuser 
        password=password10 
        dbname=blog2
    ', 
    '
        SELECT * FROM public.funct_insert2(
        '''||p_name||''',
        '''||p_detail||'''
        );
    '
    );

    RETURN query
    SELECT ('SUKSES')::character varying;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Bayu Saputro Aji
  • 1
  • 1
    Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Oct 25 '21 at 02:32

1 Answers1

0

Your code is little bit messy. Try start with reading documentation to PLpgSQL. There are three issues:

  1. PL/pgSQL doesn't allow implicit throwing result of any query. You should to iterate over result (FOR IN SELECT) or you have to save result to variable (SELECT INTO). If you want to do it, then you should to use PERFORM command. So you query should to look like:
PERFORM public.dblink(' .....');

  1. There is not any sense why your function is declared like RETURNS SETOF varchar. Then you have to use RETURN QUERY SELECT 'success'. It is absolutely useless. There is relatively high overhead of this functionality. This should be classic scalar function that returns text or better, that returns void type. The exception is raised on error. So you don't need to returns anything.

  2. Second, don't do this - PostgreSQL has FDW interface. You can use foreign table instead. It will be faster and safer.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94