CODEWITHSUNDEEP
postgresqlparametersplpgsqlassign
Perceval Dev
Perceval Dev

Reputation: 15

PLPGSQL how to use function parameters?

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'

Upvotes: 0

Views: 168

Answers (2)

jian
jian

Reputation: 4824

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

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

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;

Upvotes: 1

Related Questions