pggeodb.nancy.inra.fr/db_robot - db_robot on pggeodb.nancy.inra.fr

Function: lockrow

Schema

public

Owner

albenard

Descriptions

args: a_schema_name, a_table_name, a_row_key, an_auth_token, expire_dt - Set lock/authorization for specific row in table

Options

Definition

CREATE OR REPLACE FUNCTION public.lockrow (
 text,
 text,
 text,
 text,
 timestamp
)
RETURNS integer AS
$span$
DECLARE
myschema alias for $1;
mytable alias for $2;
myrid   alias for $3;
authid alias for $4;
expires alias for $5;
ret int;
mytoid oid;
myrec RECORD;

BEGIN

IF NOT LongTransactionsEnabled() THEN
RAISE EXCEPTION 'Long transaction support disabled, use EnableLongTransaction() to enable.';
END IF;

EXECUTE 'DELETE FROM authorization_table WHERE expires < now()';

SELECT c.oid INTO mytoid FROM pg_class c, pg_namespace n
WHERE c.relname = mytable
AND c.relnamespace = n.oid
AND n.nspname = myschema;

-- RAISE NOTICE 'toid: %', mytoid;

FOR myrec IN SELECT * FROM authorization_table WHERE
toid = mytoid AND rid = myrid
LOOP
IF myrec.authid != authid THEN
RETURN 0;
ELSE
RETURN 1;
END IF;
END LOOP;

EXECUTE 'INSERT INTO authorization_table VALUES ('||
quote_literal(mytoid::text)||','||quote_literal(myrid)||
','||quote_literal(expires::text)||
','||quote_literal(authid) ||')';

GET DIAGNOSTICS ret = ROW_COUNT;

RETURN ret;
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100;

COMMENT ON FUNCTION public.lockrow(text, text, text, text, timestamp)
IS 'args: a_schema_name, a_table_name, a_row_key, an_auth_token, expire_dt - Set lock/authorization for specific row in table';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 26/02/2014 11:51