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

8.2. Creating Object 1210 Files