PLPGSQL how to use function parameters?

Question

WORKING CODE AT THE END

I'm trying to get along with plpgsql but it's giving me a hard time. I'm trying to make a function on the database that will be called by my server to expand or create my terrain. I can't make it compile, no matter what I try, it blocks on the first usage of one of the two parameters the function has.

I havec tried sevral manners of declaring the parameters (refering to them as ALIAS FOR $1 or declaring them with a name as the following code shows) I also tried to change the parameter type to INTEGER or NUMERIC.

CREATE FUNCTION public.generate_terrain (
  inner NUMERIC,
  outer NUMERIC
)
RETURNS void AS
$body$
DECLARE
q NUMERIC;
r NUMERIC;
BEGIN
q := -outer;
r := -outer;
WHILE q < outer DO
    WHILE r < outer DO
        IF(r > -inner AND r < inner AND q > -inner AND q > inner) THEN
            r := inner;
        END IF;;
        --insert into public.t_cell (coo_q, coo_r) values (q,r);
        RAISE NOTICE 'Cell %,%', q,r;
        r := r + 1;
    END WHILE;
    q := q + 1;
END WHILE;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE;

I get this error message when I try to compile it :

ERROR:  syntax error at end of input
LINE 8: q := -outer;
                   ^

I cannot have my client do the loop and then push the results onto the database it would generate to much traffic I want to have my database do this on it's own thus the need to be able to compile this. Please Help !

EDIT 1

I forgot to explain what i wanted this function to do : I need a function that populates my database with coherent "cells" of a carthesian grid map (q = x axis, r = y axis because in reality it's hexagonal map). This function must be able to be called to expand them map so if my initial call is generate_terrain(0,2) it must produce the followin terrain :

#####
#####
##0##
#####
#####

(0 is the center of the grid (0,0)) where the coordinates range from (-2,-2) as bottom left up to (2,2) on the top right corner. Later, when i need to expand the map I must be able to call generate_terrain(3,4) to generate the following cells of my terrain :

#########
#########
##     ##
##     ##
##  0  ##
##     ##
##     ##
#########
#########

(0 is the center of the grid (0,0)) Where the coordinates range from (-4,-4) as bottom left up to (4,4) on the top right corner but the inner "square" is already present in the database

The function I ended up using and that seems to work is the following :

CREATE OR REPLACE FUNCTION public.generate_terrain (
  _inner integer,
  _outer integer
)
RETURNS integer AS
$body$
DECLARE
q integer = 0;
r integer = 0;
BEGIN
q := q - _outer;
r := r - _outer;

WHILE q <= _outer 
LOOP
    WHILE r <= _outer 
    LOOP
        -- This condition is to skip the inner square that is already
        -- present in the database.
        IF r > -_inner 
        AND r < _inner 
        AND q > -_inner 
        AND q < _inner THEN
            r := _inner;
        END IF;
        --insert into public.t_cell (coo_q, coo_r) values (q, r);
        RAISE NOTICE 'Cell %,%', q,r;
        r := r + 1;
    END LOOP;
    q := q + 1;
    r := - _outer;
END LOOP;
RETURN 1;
END;
$body$
LANGUAGE 'plpgsql'

inner and outer are reserved keywords. You can't use them as identifiers. Use different names and your problem goes away — user330315
– user330315, Commented Jul 9, 2019 at 14:22
If you insist on using those names, you can surround them in double quotes. That would be annoying for me though. Just don't use reserved keywords for variable names. — J Spratt
– J Spratt, Commented Jul 9, 2019 at 14:39

Erwin Brandstetter · Accepted Answer · 2019-07-09 15:03:09Z

1

Besides using reserved words like a_horse pointed out, you have several syntax violations. This would work:

CREATE OR REPLACE FUNCTION public.generate_terrain (_inner NUMERIC, _outer NUMERIC)
  RETURNS void AS
$func$
DECLARE
   q NUMERIC := -_outer;
   r NUMERIC := -_outer;
BEGIN
WHILE q < _outer
LOOP                           -- !
    WHILE r < _outer
    LOOP                       -- !
        IF r > -_inner
       AND r <  _inner
       AND q > -_inner 
       AND q >  _inner THEN    -- ??
            r := _inner;
        END IF;                -- !
        --insert into public.t_cell (coo_q, coo_r) values (q,r);
        RAISE NOTICE 'Cell %,%', q,r;
        r := r + 1;
    END LOOP;                  -- !
    q := q + 1;
END LOOP;                      -- !
END
$func$  LANGUAGE plpgsql;

But this seems needlessly twisted. _inner is never used at all. Did you by any chance mean to write q < _inner? (Still odd.)

Else you can just use this instead:

CREATE OR REPLACE FUNCTION public.generate_terrain_simple (_outer int)
  RETURNS void AS
$func$
INSERT INTO public.t_cell (coo_q, coo_r)
SELECT -_outer, g FROM generate_series (-_outer, _outer -1) g
$func  LANGUAGE sql;

edited Jul 9, 2019 at 15:03

answered Jul 9, 2019 at 14:57

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

Perceval Dev Over a year ago

Thank you very much ! I did make sevral mistakes here sorry, I indeed meant to write q < _inner. I also diden't mention what this function was supposed to do : I wanted it to generate cells in a carthesian grid map as a square, but i must be able to call it to expand the square thus the "_inner" square that must be skipped as it has already been inserted into the database.

jian · Accepted Answer · 2022-02-19 08:44:37Z

0

Minor optimization for Erwin Last Query.

create or replace function
    public.generate_terrain_simple_1(_outer int)
returns void as $$
    declare _x int; _y int;
    begin
    <<test>>
    for _x, _y in
    select -_outer, g from generate_series(-_outer,_outer - 1) g
    loop
    raise info 'test % %', _x,_y;
    end loop test;
    end
    $$ language plpgsql;

select * from generate_terrain_simple_1(4);

_x will be the same as -4, _y will from -4 to 3

answered Feb 19, 2022 at 8:44

jian

5,0231 gold badge29 silver badges47 bronze badges

Collectives™ on Stack Overflow

PLPGSQL how to use function parameters?

EDIT 1

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT 1

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related