<!--

$Header: /cvsroot/pgsql/doc/src/sgml/Attic/plsql.sgml,v 2.42 2001/10/09 04:55:11 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/Attic/plsql.sgml,v 2.43 2001/10/12 21:19:09 momjian Exp $

-->

<chapter id="plpgsql">

when you reload the file, it'll drop your functions and then

re-create them. For example:

<programlisting>

drop function testfunc(integer);

create function testfunc(integer) returns integer as '

DROP FUNCTION testfunc(integer);

CREATE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS '

....

end;

' language 'plpgsql';

user_id := users_rec.user_id;

...

create function cs_refresh_one_mv(integer) returns integer as '

CREATE FUNCTION cs_refresh_one_mv(INTEGER) RETURNS INTEGER AS '

DECLARE

key ALIAS FOR $1;

table_data cs_materialized_views%ROWTYPE;

is a difference between what these two functions do:

<programlisting>

CREATE FUNCTION logfunc1 (text) RETURNStimestamp AS '

CREATE FUNCTION logfunc1 (TEXT) RETURNSTIMESTAMP AS '

DECLARE

logtxt ALIAS FOR $1;

BEGIN

and

<programlisting>

CREATE FUNCTION logfunc2 (text) RETURNStimestamp AS '

CREATE FUNCTION logfunc2 (TEXT) RETURNSTIMESTAMP AS '

DECLARE

logtxt ALIAS FOR $1;

curtime timestamp;

a_output varchar(4000);

BEGIN

a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar)

RETURNSvarchar AS ''''

RETURNSVARCHAR AS ''''

DECLARE

v_host ALIAS FOR $1;

v_domain ALIAS FOR $2;

RAISE NOTICE ''i is %'',i;

END LOOP;

FOR i IN REVERSE1..10 LOOP

FOR i IN REVERSE10..1 LOOP

-- some expressions here

END LOOP;

</programlisting>

<para>

<programlisting>

create function cs_refresh_mviews () returnsinteger as '

create function cs_refresh_mviews () returnsINTEGER as '

DECLARE

mviews RECORD;

</para>

<programlisting>

CREATE FUNCTION add_one (integer) RETURNSinteger AS '

CREATE FUNCTION add_one (integer) RETURNSINTEGER AS '

BEGIN

RETURN $1 + 1;

END;

</para>

<programlisting>

CREATE FUNCTION concat_text (text, text) RETURNStext AS '

CREATE FUNCTION concat_text (TEXT, TEXT) RETURNSTEXT AS '

BEGIN

RETURN $1 || $2;

END;

</para>

<programlisting>

CREATE FUNCTION c_overpaid (EMP,integer) RETURNSboolean AS '

CREATE FUNCTION c_overpaid (EMP,INTEGER) RETURNSBOOLEAN AS '

DECLARE

emprec ALIAS FOR $1;

sallim ALIAS FOR $2;

PostgreSQL:

<programlisting>

DROP FUNCTION cs_fmt_browser_version(varchar, varchar);

CREATE FUNCTION cs_fmt_browser_version(varchar, varchar)

RETURNSvarchar AS '

DROP FUNCTION cs_fmt_browser_version(VARCHAR, VARCHAR);

CREATE FUNCTION cs_fmt_browser_version(VARCHAR, VARCHAR)

RETURNSVARCHAR AS '

DECLARE

v_name ALIAS FOR $1;

v_version ALIAS FOR $2;

Here is how this function would end up in PostgreSQL:

<programlisting>

CREATE FUNCTION cs_update_referrer_type_proc() RETURNSinteger AS '

CREATE FUNCTION cs_update_referrer_type_proc() RETURNSINTEGER AS '

DECLARE

referrer_keys RECORD; -- Declare a generic record to be used in a FOR

a_output varchar(4000);

BEGIN

a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar)

RETURNSvarchar AS ''''

a_output := ''CREATE FUNCTION cs_find_referrer_type(VARCHAR,VARCHAR,VARCHAR)

RETURNSVARCHAR AS ''''

DECLARE

v_host ALIAS FOR $1;

v_domain ALIAS FOR $2;

Here is how this procedure could be translated for PostgreSQL:

<programlisting>

drop function cs_parse_url_host(varchar);

create function cs_parse_url_host(varchar) returns varchar as '

drop function cs_parse_url_host(VARCHAR);

create function cs_parse_url_host(VARCHAR) RETURNS VARCHAR AS '

declare

v_url ALIAS FOR $1;

v_host varchar;

So let's see one of the ways we could port this procedure to <application>PL/pgSQL</>:

<programlisting>

drop function cs_create_job(integer);

create function cs_create_job(integer) returns integer as 'declare

v_job_idalias for $1;

a_running_job_countinteger;

drop function cs_create_job(INTEGER);

create function cs_create_job(INTEGER) RETURNS INTEGER AS 'DECLARE

v_job_idALIAS FOR $1;

a_running_job_countINTEGER;

a_num integer;

-- pragma autonomous_transaction;

begin

lock table cs_jobs in exclusive mode;

select count(*) into a_running_job_count from cs_jobs where end_stamp is null;

if a_running_job_count > 0 then

-- commit; -- free lock

raise exception ''Unable to create a new job: a job is currently running.'';

end if;

BEGIN

LOCK TABLE cs_jobs IN EXCLUSIVE MODE;

SELECT count(*) INTO a_running_job_count

FROM cs_jobs

WHERE end_stamp IS NULL;

IF a_running_job_count > 0

THEN

-- COMMIT; -- free lock

RAISE EXCEPTION ''Unable to create a new job: a job is currently running.'';

END IF;

delete from cs_active_job;

insert into cs_active_job(job_id)values(v_job_id);

DELETE FROM cs_active_job;

INSERT INTO cs_active_job(job_id)VALUES(v_job_id);

SELECT count(*) into a_num FROM cs_jobs WHERE job_id=v_job_id;

SELECT count(*) into a_num

FROM cs_jobs

WHERE job_id=v_job_id;

IF NOT FOUND THEN -- If nothing was returned in the last query

-- This job is not in the table so lets insert it.

insert into cs_jobs(job_id, start_stamp)values(v_job_id, sysdate());

return 1;

INSERT INTO cs_jobs(job_id, start_stamp)VALUES(v_job_id, sysdate());

RETURN 1;

ELSE

raise NOTICE ''Job already running.'';<co id="co.plpgsql-porting-raise">

RAISE NOTICE ''Job already running.'';<co id="co.plpgsql-porting-raise">

END IF;

return 0;

end;

'language 'plpgsql';

RETURN 0;

END;

'LANGUAGE 'plpgsql';

</programlisting>

<calloutlist>

package would become something like this:

<programlisting>

CREATE FUNCTION acs__add_user(integer,integer,varchar,datetime,integer,integer,...)

RETURNSinteger AS '

CREATE FUNCTION acs__add_user(INTEGER,INTEGER,VARCHAR,DATETIME,INTEGER,INTEGER,...)

RETURNSINTEGER AS '

DECLARE

user_id ALIAS FOR $1;

object_type ALIAS FOR $2;

v_user_id := acs_user__new(user_id,object_type,creation_date,creation_user,creation_ip, ...);

...

return v_user_id;

RETURN v_user_id;

END;

' LANGUAGE 'plpgsql';

</programlisting>

FUNCTION</command> statement. Something like:

<programlisting>

CREATE FUNCTION foo(...) RETURNSinteger AS '

CREATE FUNCTION foo(...) RETURNSINTEGER AS '

...

' LANGUAGE 'plpgsql'

WITH (isstrict, iscachable);

--

DROP FUNCTION instr(varchar,varchar);

CREATE FUNCTION instr(varchar,varchar) RETURNSinteger AS '

CREATE FUNCTION instr(VARCHAR,VARCHAR) RETURNSINTEGER AS '

DECLARE

pos integer;

BEGIN

' language 'plpgsql';

DROP FUNCTION instr(varchar,varchar,integer);

CREATE FUNCTION instr(varchar,varchar,integer) RETURNSinteger AS '

DROP FUNCTION instr(VARCHAR,VARCHAR,INTEGER);

CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER) RETURNSINTEGER AS '

DECLARE

string ALIAS FOR $1;

string_to_search ALIAS FOR $2;

-- Written by Robert Gaszewski (graszew@poland.com)

-- Licensed under the GPL v2 or later.

--

DROP FUNCTION instr(varchar,varchar,integer,integer);

CREATE FUNCTION instr(varchar,varchar,integer,integer) RETURNSinteger AS '

DROP FUNCTION instr(VARCHAR,VARCHAR,INTEGER,INTEGER);

CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNSINTEGER AS '

DECLARE

string ALIAS FOR $1;

string_to_search ALIAS FOR $2;

beg_index ALIAS FOR $3;

occur_index ALIAS FOR $4;

pos integer NOT NULL DEFAULT 0;

occur_number integer NOT NULL DEFAULT 0;

temp_strvarchar;

beginteger;

iinteger;

lengthinteger;

ss_lengthinteger;

temp_strVARCHAR;

begINTEGER;

iINTEGER;

lengthINTEGER;

ss_lengthINTEGER;

BEGIN

IF beg_index > 0 THEN

beg := beg_index;

RETURN 0;

END IF;

END;

'language 'plpgsql';

'LANGUAGE 'plpgsql';

</programlisting>

</sect3>

</sect2>