Scripts de prueba para funciones en PostgreSQL...

-- Tabla de prueba

CREATE TABLE serverb
(
  id serial NOT NULL,
  name character varying(20),
  ip character varying(15)
)

-- Funcion generica

CREATE FUNCTION somefunc() RETURNS integer AS $$

<< outerblock >>

DECLARE

    quantity integer := 30;

BEGIN

    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30

    quantity := 50;

    --

    -- Create a subblock

    --

    DECLARE

        quantity integer := 80;

    BEGIN

        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80

        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50

    END;

    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50

    RETURN quantity;

END;

$$ LANGUAGE plpgsql;

-- Ejemplo usando serverb

CREATE FUNCTION recservers() RETURNS SETOF serverb AS $$

    SELECT * FROM serverb;

$$ LANGUAGE SQL;

select * from serverb;

2;"xserver02";"10.10.1.254"

select recservers();

"(2,xserver02,10.10.1.254)"

select * from recservers();

2;"xserver02";"10.10.1.254"

-- Ejemplo generando como resultado una tabla nueva

CREATE FUNCTION query_1 (x int)

RETURNS TABLE(totid int, name text) AS $$

    SELECT id+$1, name FROM serverb;

$$ LANGUAGE SQL;

select * from query_1(10);

12;"xserver02"

-- Ejemplo insertando registros

CREATE FUNCTION insertserver(pid int, pname text, pip text) RETURNS integer AS $$

  BEGIN

insert into serverb values($1, $2, $3);

RETURN 1;

  END;

$$ LANGUAGE "plpgsql"

select insertserver(3,'iw','1.1.1.1');