Execute Select query in end of function

Question

I have CTE as bellow,

WITH extract_dow AS (
SELECT EXTRACT(DOW FROM TIMESTAMP '1994-03-15'::DATE) AS dow
)
,name_day AS(
SELECT
CASE WHEN dow = 0 THEN (SELECT 'Minggu'::TEXT AS day)
    ELSE
    CASE WHEN dow = 1 THEN (SELECT 'Senin'::TEXT AS day)
        ELSE
        CASE WHEN dow = 2 THEN (SELECT 'Selasa'::TEXT AS day)
            ELSE
            CASE WHEN dow = 3 THEN (SELECT 'Rabu'::TEXT AS day)
                ELSE
                CASE WHEN dow = 4 THEN (SELECT 'Kamis'::TEXT AS day)
                    ELSE
                    CASE WHEN dow = 5 THEN (SELECT 'Jumat'::TEXT AS day)
                        ELSE
                        CASE WHEN dow = 6 THEN (SELECT 'Sabtu'::TEXT AS day)
                            ELSE (SELECT 'Inputan Salah'::TEXT AS day)
                        END
                    END
                END
            END
        END
    END
END
FROM extract_dow
)

, extract_age AS (
SELECT ((CURRENT_DATE - '1994-03-15'::DATE)/365) AS age
)

SELECT 'Anda Lahir :' || a.day || ',' || b.age || ' Tahun'  AS "Keterangan" FROM name_day a, extract_age b

Its to generate day in text of date '1994-03-15' and age calculate from current, when its execute we have in output:

"Anda Lahir :Selasa,24 Tahun"

When I Try to convert it to function as bellow:

> -- Function: function_birthday(date)
> 
> -- DROP FUNCTION function_birthday(date);
> 
> CREATE OR REPLACE FUNCTION function_birthday(inputan date)   
> RETURNS text AS 
> $BODY$ 
> DECLARE   
> perintah text; 
> BEGIN
>
> perintah :=  $sql$
> 
>   WITH extract_dow AS (   SELECT EXTRACT(DOW FROM TIMESTAMP '$sql$ || inputan || $sql$'::DATE) AS dow     )
>   ,name_day AS(   SELECT  CASE WHEN dow =  0 THEN (SELECT 'Minggu'::TEXT AS day)
>       ELSE CASE WHEN dow = 1 THEN (SELECT 'Senin'::TEXT AS day)
>           ELSE 
>           CASE WHEN dow = 2 THEN (SELECT 'Selasa'::TEXT AS day)
>               ELSE
>               CASE WHEN dow = 3 THEN (SELECT 'Rabu'::TEXT AS day)
>                   ELSE
>                   CASE WHEN dow = 4 THEN (SELECT 'Kamis'::TEXT AS day)
>                       ELSE
>                       CASE WHEN dow = 5 THEN (SELECT 'Jumat'::TEXT AS day)
>                           ELSE
>                           CASE WHEN dow = 6 THEN (SELECT 'Sabtu'::TEXT AS day)
>                               ELSE (SELECT 'Inputan Salah'::TEXT AS day)
>                           END
>                       END
>                   END
>               END             
>           END
>      END
> END FROM extract_dow  )
>
>
>   , extract_age AS (  SELECT ((CURRENT_DATE - '$sql$ || inputan || $sql$'::DATE)/365) AS age)
> SELECT 'Anda Lahir :' || a.day || ',' || b.age || ' Tahun'  AS "Keterangan" FROM name_day a, extract_age b
> 
> RETURN *;
> $sql$;
> EXECUTE perintah;      
> END;
> $BODY$
> LANGUAGE plpgsql VOLATILE   COST 100;
> ALTER FUNCTION function_birthday(date)   
> OWNER TO postgres;

But when im execute it with

SELECT public.function_birthday('1994-03-15');

I have error

ERROR: syntax error at or near "RETURN"

LINE 36: RETURN *;

CONTEXT: PL/pgSQL function function_birthday(date) line 43 at EXECUTE statement

********** Error **********

ERROR: syntax error at or near "RETURN"

SQL state: 42601

Context: PL/pgSQL function function_birthday(date) line 43 at EXECUTE statement

My question is, where is the error from my function, or i am write in wrong structure of the function, i had searching and looking for to solve it (How to convert my CTE to function)?

Thanks for Help, ^_^

You cannot use return from plain SQL query. You assign the result of the query to a variable and then return it. — mmontoya
So we cant get same/identical output CTE/query and function? — M. Fitriawan

mmontoya mmontoya · Accepted Answer · 2018-12-04T03:24:10

You could try:

> -- Function: function_birthday(date)
> 
> -- DROP FUNCTION function_birthday(date);
> 
> CREATE OR REPLACE FUNCTION function_birthday(inputan date)   
> RETURNS text AS 
> $BODY$ 
> DECLARE   
> perintah text; 
> result text;
> BEGIN
>
> perintah :=  $sql$
> 
>   WITH extract_dow AS (   SELECT EXTRACT(DOW FROM TIMESTAMP '$sql$ || inputan || $sql$'::DATE) AS dow     )
>   ,name_day AS(   SELECT  CASE WHEN dow =  0 THEN (SELECT 'Minggu'::TEXT AS day)
>       ELSE CASE WHEN dow = 1 THEN (SELECT 'Senin'::TEXT AS day)
>           ELSE 
>           CASE WHEN dow = 2 THEN (SELECT 'Selasa'::TEXT AS day)
>               ELSE
>               CASE WHEN dow = 3 THEN (SELECT 'Rabu'::TEXT AS day)
>                   ELSE
>                   CASE WHEN dow = 4 THEN (SELECT 'Kamis'::TEXT AS day)
>                       ELSE
>                       CASE WHEN dow = 5 THEN (SELECT 'Jumat'::TEXT AS day)
>                           ELSE
>                           CASE WHEN dow = 6 THEN (SELECT 'Sabtu'::TEXT AS day)
>                               ELSE (SELECT 'Inputan Salah'::TEXT AS day)
>                           END
>                       END
>                   END
>               END             
>           END
>      END
> END FROM extract_dow  )
>
>
>   , extract_age AS (  SELECT ((CURRENT_DATE - '$sql$ || inputan || $sql$'::DATE)/365) AS age)
> SELECT 'Anda Lahir :' || a.day || ',' || b.age || ' Tahun'  AS "Keterangan" FROM name_day a, extract_age b
> 
> $sql$;
> EXECUTE perintah INTO result;      
> RETURN result;    
> END;
> $BODY$
> LANGUAGE plpgsql VOLATILE   COST 100;
> ALTER FUNCTION function_birthday(date)   
> OWNER TO postgres;

Execute Select query in end of function

1 Answers