8.1. Requêtes SQL
8.1.1. Before Starting
Before proceeding to actions described further, SQL-queries for this step should be performed.
8.1.2. Creating a Function that Returns Filename
CREATE OR REPLACE FUNCTION f_get_filename(_filename bytea)
RETURNS text AS
$BODY$
declare
len integer;
begin
len := "position"(_filename, E'\\000'::bytea)-1;
if len > 0 then
return convert_from("substring"(_filename, 1, len ), 'WIN1251');
end if;
return NULL::text;
end;
$BODY$
LANGUAGE plpgsql VOLATILE;
8.1.3. Creating a View to Display Files
CREATE OR REPLACE VIEW vw_files AS
SELECT t_files.file_id,
t_files.dttmcr,
t_files.dttmup,
t_files.dttmcl,
t_files.userid,
t_files.comment_id,
f_get_filename(t_files.comment_file) AS filename
FROM t_files;
8.1.4. Creating a Function to Download Files from the Database
CREATE OR REPLACE FUNCTION f_get_file("1210.file_id" integer)
RETURNS SETOF bytea AS
$BODY$
BEGIN
RETURN QUERY
SELECT comment_file
FROM t_files
WHERE file_id = $1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
8.1.5. Creating a Function to Upload Files to the Database
CREATE OR REPLACE FUNCTION f_upload_file(
pdb_userid integer,
"1202.comment_id" integer,
pdb_file_data bytea)
RETURNS boolean AS
$BODY$
BEGIN
INSERT INTO t_files (userid, comment_id, comment_file)
VALUES (pdb_userid, "1202.comment_id", pdb_file_data);
RETURN true;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
8.1.6. Creating a Function to Close Files
CREATE OR REPLACE FUNCTION f_close_file("1210.file_id" integer)
RETURNS boolean AS
$BODY$
BEGIN
UPDATE t_files
SET dttmcl = now() WHERE t_files.file_id = "1210.file_id";
RETURN true;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
8.1.7. Next