CODEWITHSUNDEEP
c#postgresqlstored-procedures
Tony Vitabile
Tony Vitabile

Reputation: 8594

PostgreSql Large Objects and Stored Functions

My application receives JPG images as byte arrays from a device connected to the system. These need to be written to the PostgreSql 9.1 database's Large Objects table. I'm currently using C# and the Devart docConnect for PostgreSql library to access my database, and everything is working fine. The problem is that writing my data to the database (I need to update 2 tables plus write the image bytes to the large objects table) takes several trips to the database. I want to reduce the number of trips my code makes to the database to speed things up.

I have written a stored function which performs an UPSERT on the two tables. I want to pass the image bytes as a byte array parameter to the stored function and have the stored function write the image bytes to the large objects table. Is this possible? The documentation on server side functions in the PostgreSql 9.1 manual is skimpy and I'm not sure what function to call to read & write the data.

Thanks

Tony

Upvotes: 0

Views: 1259

Answers (1)

Twelfth
Twelfth

Reputation: 7180

Unlike Oracle or Microsoft, Postgres does not differentiate between functions and stored procedures...it has only functions (look up in the postgres manual about functions...very flexible as well, you can call c or java libraries and other fun stuffs). So you'll create a function (probably in PLPGSQL) to accept a few parameters, then call the function using a select statement as function(arguement1,arg2,etc...). Here is an example if you want a template:

CREATE OR REPLACE FUNCTION deleteme
(deleteid integer)
RETURNS integer AS
$BODY$
BEGIN
delete from mytable_attrib where id = $1;
delete from mytable where id = $1;
return 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Little different in it's language and syntax then you might be used to...do not name a variable the same name as a column name for example...or be prepared to shift to dynamic SQL to build a statement in a variable using plpgsql and execute the variable as sql.

Execute it like a function, not the exec syntax oracle or MS would like. To delete ID row 8 I can now use

select deleteme(8)

Extra points for apple spell check changing postgres to post grease

I should flag that my knowledge is for 8.4 not 9.1

Upvotes: 1

Related Questions