Last active
July 31, 2017 14:54
-
-
Save KentaYamada/e907b14f20ed01fc4e46d8d0cdb5014e to your computer and use it in GitHub Desktop.
PL/PgSQL example
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- clean up. | |
drop table if exists persons; | |
drop function if exists entry_person(text, integer); | |
-- initialize. | |
create table persons ( | |
name text | |
,age integer | |
); | |
create or replace function entry_person(p_name, p_age integer) | |
returns boolean as | |
$$ | |
declare | |
affected_row integer; | |
saved boolean; | |
begin | |
update persons set | |
name = p_name | |
,age = p_age | |
where name = p_name; | |
get dianostics affected_row = row_count; | |
if affected_row < 1 then | |
insert into persons ( | |
name | |
,age | |
) values ( | |
p_name | |
,p_age | |
); | |
end if; | |
get diagnostics affected_row = row_count; | |
if affected_row < 1 then | |
saved = FALSE; | |
else | |
saved = TRUE; | |
end if; | |
return saved; | |
end; | |
$$ language plpgsql; | |
-- testing. | |
select entry_person('Taro', 24); | |
select * from persons; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION find_persons() | |
RETURNS TABLE(name TEXT) | |
LANGUAGE 'plpgsql' | |
AS $$ | |
BEGIN | |
RETURN QUERY | |
SELECT t.name FROM persons as t | |
END; | |
$$; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment