How to implement stored procedure/function in postgresql version 12?

Question

How I can implement it? I am confused in syntax also?

CREATE OR REPLACE Function dailyyyy_volation_routine(_ispeed_count integer,_rlvd_count integer,_stopline_count integer,_wrongdir_count integer
                                                     ,_wronglane_count integer,_zebracross_count integer,_total_count integer)
RETURNS void AS $$
BEGIN
INSERT INTO temp_daily_stats(ispeed_count,rlvd_count,stopline_count,wrongdir_count,wronglane_count,zebracross_count,total_count)
with t1 as (SELECT SUM(ispeed_count) as ispeed_count from temp_hourly_stats),
t3 as (SELECT SUM(rlvd_count) as rlvd_count from temp_hourly_stats),
t4 as (SELECT SUM(stop_line_count) as stopline_count from temp_hourly_stats),
t5 as (SELECT SUM(wrong_dir_count) as wrong_dir_count from temp_hourly_stats),
t6 as (SELECT SUM(wrong_lane_count) as wrong_lane_count from temp_hourly_stats),
t7 as (SELECT SUM(zebra_cross_count) as zebra_cross_count from temp_hourly_stats),
t8 as (SELECT @tc := SUM(total_count) as total_day_count from temp_hourly_stats),
SELECT DISTINCT t1.ispeed_count,t3.rlvd_count,t4.stopline_count,t5.wrong_dir_count,t6.wrong_lane_count,t7.zebra_cross_count,t8.total_day_count
FROM t1,t3,t4,t5,t6,t7,t8 limit 1;
END
$$ 
LANGUAGE SQL;

I want to put data from one table to another using postgresql function?*

SQL functions don't use BEGIN and END. Remove those two lines. — Jeremy
– Jeremy, Commented Feb 25, 2020 at 10:26
ERROR: syntax error at or near ":=" LINE 11: t8 as (SELECT @tc := SUM(total_count) as total_day_count fro... — Utkarsh Mishra
– Utkarsh Mishra, Commented Feb 25, 2020 at 10:38
SELECT @tc := SUM(total_count) is invalid standard SQL and invalid in Postgres - what is that supposed to do? — user330315
– user330315, Commented Feb 25, 2020 at 11:26

Laurenz Albe · Accepted Answer · 2020-02-25 11:24:04Z

1

Here is an attempt to translate your function to correct code:

CREATE OR REPLACE Function dailyyyy_volation_routine(
   _ispeed_count integer,
   _rlvd_count integer,
   _stopline_count integer,
   _wrongdir_count integer,
   _wronglane_count integer,
   _zebracross_count integer,
_total_count integer) RETURNS void AS
$$INSERT INTO temp_daily_stats(
   ispeed_count,
   rlvd_count,
   stopline_count,
   wrongdir_count,
   wronglane_count,
   zebracross_count,
   total_count
)
SELECT SUM(ispeed_count),
       SUM(rlvd_count),
       SUM(stop_line_count),
       SUM(wrong_dir_count),
       SUM(wrong_lane_count),
       SUM(zebra_cross_count),
       SUM(total_count)
FROM temp_hourly_stats$$ 
LANGUAGE SQL;

edited Feb 25, 2020 at 11:24

answered Feb 25, 2020 at 7:27

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Utkarsh Mishra Over a year ago

CREATE OR REPLACE Function dailyyyy_volation_routine(ispeed_count integer) RETURNS void AS $$ BEGIN INSERT INTO temp_daily_stats(ispeed_count) SELECT SUM(ispeed_count) as ispeed_count from temp_hourly_stats; END $$ LANGUAGE SQL;

Utkarsh Mishra Over a year ago

I did the same but its saying syntax error near Insert or at insert

Utkarsh Mishra Over a year ago

I am using latest version of postgre

Laurenz Albe Over a year ago

Ah-hah, but you never said in your answer that the function has a parameter of the same name (which is bad practice). In that case, you have to qualify the column. I'll update the answer.

Laurenz Albe Over a year ago

I doubt that that is valid SQL anywhere. Anyway, I have attempted to translate your code into valid SQL in my answer.

|

Collectives™ on Stack Overflow

How to implement stored procedure/function in postgresql version 12?

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related