CODEWITHSUNDEEP
postgresqlfunctiondll
Dr. Goulu
Dr. Goulu

Reputation: 590

PostgreSQL : How to pass a request to a function

I have a table of images and need to use a C image processing library to extract the n most relevant images from it. I try to figure out what parameters will be passed by prototyping the function in SQL (dummy):

CREATE OR REPLACE FUNCTION clusterize(anyarray,integer) 
RETURNS TABLE(image bytea) AS $$
    SELECT * FROM $1
    ORDER BY random()
    LIMIT $2
$$ LANGUAGE SQL; -- will be C 

WITH subreq AS (
  SELECT decode(encode(thumbnail,'escape'), 'base64') "data",
    otherfields
  FROM images -- which actually is another WITH subrequest ...
)

SELECT clusterize(* FROM subreq,12)

generates a syntax error close to the $1. How to pass "subreq" results to the function ? In this case, does it mean that all images will be loaded in memory before calling the function, or is there an iterator mechanism ?

Is it simpler to decompose in two functions, an "addimage" function to call for each image and then a "clusterize(12)" that would return a table of images ?

Upvotes: 1

Views: 178

Answers (1)

404
404

Reputation: 8572

Your function takes anyarray but bytea isn't an "array" in the usual PG sense. Since the table you're returning is of bytea, and you're not doing any sort of cast or transformation, is there any reason not to put the actual type as the parameter?

Regardless, if you want to SELECT * from an array, you need to UNNEST it to put it into table form.

So, your clusterize function now looks like this:

CREATE OR REPLACE FUNCTION clusterize(anyarray,integer) 
RETURNS TABLE(image bytea) AS $$
    SELECT * FROM UNNEST($1)
    ORDER BY random()
    LIMIT $2
$$ LANGUAGE SQL

Now to create an actual array from the bytea data. There don't appear to be good bytea functions, so a bit of hackery does it:

WITH subreq AS (
        SELECT decode(encode(E'Th\\000omas'::bytea, 'escape'), 'escape') d
)
SELECT clusterize(ARRAY_AGG(SUBSTRING(d FROM g FOR 1)), 4)
FROM subreq, generate_series(1, LENGTH(d)) g

Result:

result

Now if we can change clusterize to take a bytea, we can hide that hacky code in there instead:

CREATE OR REPLACE FUNCTION clusterize(bytea,integer) 
RETURNS TABLE(image bytea) AS $$
    SELECT SUBSTRING($1 FROM g FOR 1)
    FROM generate_series(1, LENGTH($1)) g
    ORDER BY random()
    LIMIT $2
$$ LANGUAGE SQL

WITH subreq AS (
        SELECT decode(encode(E'Th\\000omas'::bytea, 'escape'), 'escape') d
)
SELECT clusterize(d, 4)
FROM subreq

Result:

result

Upvotes: 1

Related Questions