PL/PgSQL example

sample-plpgsql.sql

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

sample-select.sql

CREATE OR REPLACE FUNCTION find_persons()
RETURNS TABLE(name TEXT)
LANGUAGE 'plpgsql'
AS $$
BEGIN
    RETURN QUERY
    SELECT t.name FROM persons as t
END;
$$;