Declare variable PostgreSQL

Question

I'm trying to declare a variable which I will pass a value, but even with a single example, I got an different errors. For example: unexpected end of function definition at end of input. I assume that the reason is that I am passing the value to a temporary table, but I don't know how to fix it

DO $$
DECLARE var2 varchar(20) := 'dzien';
BEGIN
SELECT 
CASE WHEN var2 = 'dzien' THEN to_date((to_char(czasRozmowy, 'YYYY/MM/DD' )),'YYYY/MM/DD') ELSE to_date('0000/00/00', 'YYYY/MM/DD')
END as data_,
COUNT(DISTINCT(lekta_call_id)) iloscRozmow
,COUNT(DISTINCT(czasProcesu)) iloscProceso
,SUM(czySukces) iloscRozmowSukces
,SUM(czyTransfer) iloscRozmowTransfer
,SUM(czyRozlaczone) iloscRozmowRozlaczonych
,SUM(czyWsylanySMS) iloscSMS
FROM
(
SELECT 
c.start_time czasRozmowy
,d.process_start_time czasProcesu
,c.l_call_id l_call_id
,CASE WHEN end_call_status=3 THEN 1 ELSE 0 END czySukces
,CASE WHEN end_call_status=2 THEN 1 ELSE 0 END czyTransfer
,CASE WHEN end_call_status=1 THEN 1 ELSE 0 END czyRozlaczone
,CASE WHEN send_sms=true THEN 1 ELSE 0 END czyWsylanySMS
FROM "Conversat"  C 
left join dialogues_details d on c.l_call_id=d.l_call_id
) as tabelka
group by data_
END $$;

A do block can not return a result to begin with. Did you have a look at the many questions asking this? e.g. this or this — user330315
– user330315, Commented Nov 12, 2020 at 13:30
yes, but still can't find how to solve it, just found that I need to declare variable in a function but how to work with it idk — vitalii
– vitalii, Commented Nov 12, 2020 at 14:23
The cause of the error is that ... group by data_ should be ... group by data_ ;. You did not terminate the query. As mentioned, an anonymous function(DO) cannot return anything, so right this as a regular function. — Adrian Klaver
– Adrian Klaver, Commented Nov 12, 2020 at 15:17

Vignesh Dhanabal · Accepted Answer · 2020-11-17 16:29:26Z

Try this

CREATE OR REPLACE FUNCTION _get_result(
    _var2 VARCHAR DEFAULT NULL
) RETURNS TABLE (
    iloscRozmow INTEGER,
    iloscProceso INTEGER,
    iloscRozmowSukces INTEGER,
    iloscRozmowTransfer INTEGER,
    iloscRozmowRozlaczonych INTEGER,
    iloscSMS INTEGER,
) AS $$
BEGIN
    
    RETURN QUERY SELECT 
    CASE WHEN _var2 = 'dzien' THEN to_date((to_char(czasRozmowy, 'YYYY/MM/DD' )),'YYYY/MM/DD') ELSE to_date('0000/00/00', 'YYYY/MM/DD')
    END as data_,
    COUNT(DISTINCT(lekta_call_id)) iloscRozmow,
    COUNT(DISTINCT(czasProcesu)) iloscProceso,
    SUM(czySukces) iloscRozmowSukces,
    SUM(czyTransfer) iloscRozmowTransfer,
    SUM(czyRozlaczone) iloscRozmowRozlaczonych,
    SUM(czyWsylanySMS) iloscSMS
    FROM
    (
        SELECT 
        c.start_time czasRozmowy,
        d.process_start_time czasProcesu,
        c.l_call_id l_call_id,
        CASE WHEN end_call_status=3 THEN 1 ELSE 0 END czySukces,
        CASE WHEN end_call_status=2 THEN 1 ELSE 0 END czyTransfer,
        CASE WHEN end_call_status=1 THEN 1 ELSE 0 END czyRozlaczone,
        CASE WHEN send_sms=true THEN 1 ELSE 0 END czyWsylanySMS
        FROM "Conversat"  C 
        left join dialogues_details d on c.l_call_id=d.l_call_id
    ) as tabelka
    group by data_;

END
$$ LANGUAGE 'plpgsql';

SELECT * FROM _get_result('dzien');

Collectives™ on Stack Overflow

Declare variable PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related