function does not exist error in PostgreSQL-CodePudding

I'm writing procedure in PostgreSQL While writing am getting error for FN_GETCD like function fn_getcd(integer) does not exist IS THE NEW VALUE but i have written function for fn_getcd like this

   CREATE OR REPLACE FUNCTION public.fn_getcd(
        v_entity_num text)
        RETURNS timestamp without time zone
        LANGUAGE 'plpgsql'
        COST 100
        STABLE SECURITY DEFINER PARALLEL UNSAFE
    AS $BODY$
  

    DECLARE
    
        O_CBD timestamp;
    
    BEGIN
        SELECT
          TO_DATE(TO_CHAR(clock_timestamp(),'DD-MON-YYYY'),'DD-MON-YYYY')
        INTO STRICT
          O_CBD
    ;
        RETURN O_CBD;
      EXCEPTION
      WHEN OTHERS THEN
        RETURN O_CBD;
      END;
    $BODY$;

please help me to solve this error

 tocheck :=cast(P_ENTITY_CODE as TEXT);
  RAISE NOTICE 'GOT HERE tocheck :% IS THE NEW VALUE',tocheck;
         EXECUTE 'INSERT INTO BENMASTHIST SELECT BENMAST_ENTITY_CODE, BENMAST_CUSTOMER_CODE, BENMAST_BEN_CODE,
                      FN_GETCD(' ||
                              tocheck || '),' || W_BEN_HIST_SL ||
                              ', BENMAST_NAME, BENMAST_MOBILE_NO,
                      BENMAST_EMAIL_ID, BENMAST_PHOTO, BENMAST_SOURCE, BENMAST_CR_BY, BENMAST_CR_ON,
                      BENMAST_MO_BY, BENMAST_MO_ON, BENMAST_AU_BY, BENMAST_AU_ON, TBA_KEY,BENMAST_FROM
                      FROM BENMAST WHERE BENMAST_ENTITY_CODE =$1 AND BENMAST_CUSTOMER_CODE =$2 AND BENMAST_BEN_CODE =$3'
              USING P_ENTITY_CODE, P_CUST_CODE, P_BEN_CODE;
-- RAISE NOTICE 'GOT HERE BENMASTHIST :% IS THE NEW VALUE',BENMASTHIST;
              
          EXCEPTION
            WHEN UNIQUE_VIOLATION THEN
              P_SUC_FLAG := 'S';
            WHEN OTHERS THEN
            ---    DBMS_OUTPUT.put_line(SQLERRM);
              P_SUC_FLAG := 'F';
               P_ERROR    := SQLERRM;
              RAISE NOTICE 'SQLERRM :% IS THE NEW VALUE',SQLERRM;
              P_ERROR    := 'BENREG013'; --'Error While Inserting Into Benmast';
            --  ROLLBACK;
          END;

CodePudding user response：

reference: dollar quote: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

Sometimes you don't know how much quote you need.You can first compose the string then do the execute command.

a horse already said that you function can be as simple as return current_date::timestamp;

If you add more quote then you function will work.

DO $$
DECLARE
    _vstr text := 'test';
    _vtime timestamp;
    _sql text;
    _sql1 text;
    _sql0 text;
BEGIN
    _sql := ' select fn_getcd( ' || '''' || _vstr || '''' || ' )';
    _sql1 := ' select fn_getcd( ' || $a$ '$a$ || _vstr  || $b$' $b$ || ' )';
    _sql0 := ' select fn_getcd( ' || _vstr || ' )';
    RAISE NOTICE '_sql is %', _sql;
    RAISE NOTICE '_sql1 is %', _sql1;
    RAISE NOTICE '_sql1 is %', _sql0;
    EXECUTE _sql INTO _vtime;
    RAISE NOTICE 'v_time is % ', _vtime;
END
$$
LANGUAGE plpgsql;

You string is like _sql0 format. You need use dollar quote or simple put more single quote in it. (_sql or _sql1).