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');