开发笔记:PostgreSQL存储过程

CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$
DECLARE
declaration;
[...]
BEGIN
< function_body >
[...]
RETURN { variable_name | value }
END; LANGUAGE plpgsql;

select mycount3()

create or replace function mycount3()
returns integer as $$
declare
mysql text;
counts integer;
begin
mysql:=\'select count("CD_ID") from "CDS"\';
execute mysql into counts;
return counts;
end;
$$ language plpgsql;

create or replace function mycount4(tableName text, columnName text)
returns text as $$
declare
mysql text;
begin
mysql:=\'select count(\'
|| quote_ident(columnName)
|| \') from \'
|| quote_ident(tableName);
return mysql;
end;
$$ language plpgsql;

select mycount4(\'CDS\', \'CD_ID\');

select count("CD_ID") from "CDS"

create or replace function mycount4(tableName text, columnName text)
returns integer as $$
declare
mysql text;
counts integer;
begin
mysql:=\'select count("\' || $2 || \'") from "\' || $1 || \'" \';
execute mysql into counts using tableName, columnName;
if counts > 100 then
return counts;
else return 1;
end if;
end;
$$ language plpgsql;