ranyeli
2/8/2018 - 9:22 PM

Store Procedures

Create a store procedure (function) on postgresql

var
  set1 : TDataSet;

begin
//  FDStoredProc1.ExecProc('family4');
//  FDStoredProc1.Close;
  FDStoredProc1.Open('family4');
  set1 := ListDataSource.DataSet;
  try
    DBListBox1.Items.Clear;
    while not set1.Eof do
      begin
        DBlistbox1.items.add(set1.FieldByName('name').AsString);
        set1.Next;
      end;
  finally
//    set1.Close;
  end;

end;
-- FUNCTION: public.get_family

-- DROP FUNCTION public.get_family;
rollback;
CREATE OR REPLACE FUNCTION public.get_family2(
	)
    RETURNS refcursor
    LANGUAGE 'plpgsql'
    COST 100.0
    VOLATILE 
AS $function$

declare
	ref refcursor = 'cursor_f';
begin 
	open ref for select * from lopez;
    return ref;
end;

$function$;

ALTER FUNCTION public.get_family()
    OWNER TO postgres;
-- Procedure that returns multiple result sets (cursors)
 CREATE OR REPLACE FUNCTION show_cities_multiple() RETURNS SETOF refcursor AS $$
  DECLARE
    ref1 refcursor;           -- Declare cursor variables
    ref2 refcursor;                             
  BEGIN
    OPEN ref1 FOR SELECT city, state FROM cities WHERE state = 'CA';   -- Open the first cursor
    RETURN NEXT ref1;                                                                              -- Return the cursor to the caller

    OPEN ref2 FOR SELECT city, state FROM cities WHERE state = 'TX';   -- Open the second cursor
    RETURN NEXT ref2;                                                                              -- Return the cursor to the caller
  END;
  $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION show_cities() RETURNS refcursor AS $$
  DECLARE
    ref refcursor;
  BEGIN
    OPEN ref FOR SELECT city, state FROM cities;
    RETURN ref;
  END;
  $$ LANGUAGE plpgsql;
-- Procedure that returns a cursor (its name specified as the parameter)
 CREATE OR REPLACE FUNCTION show_cities2(ref refcursor) RETURNS refcursor AS $$
  BEGIN
    OPEN ref FOR SELECT city, state FROM cities;    -- Open a cursor
    RETURN ref;                                     -- Return the cursor to the caller
  END;
  $$ LANGUAGE plpgsql;

-- to be able to see cursor content...
-- select show_cities2('the_cursor_name');
-- FETCH ALL IN "the_cursor_name";
-- Procedure to insert a new city
  CREATE OR REPLACE FUNCTION add_city(city VARCHAR(70), state CHAR(2)) 
  RETURNS void AS $$
  BEGIN
    INSERT INTO cities VALUES (city, state);
  END;
  $$ LANGUAGE plpgsql;

-- Add a new city
-- SELECT add_city('St.Louis', 'MO');