CODEWITHSUNDEEP
sqlpostgresqluser-defined-functions
Muhit
Muhit

Reputation: 789

How a function can take array or set values as parameter(IN) for an user defined function in PostgreSQL

I was about to make an user defined function to implement faceted search in a hypergraph based database where the function will return facets with counts. problem is I need to pass the set of fields to be searched in. I am not sure how to do it. Please have a look at the function and help me. I want to pass search_fields somewhat like {'node_type', 'node_title', 'title'} etc.

CREATE OR REPLACE FUNCTION facets(
    IN keyword text, IN search_fields text, IN relation_name text, IN node_type text, IN group_by text, 
    OUT facet text, OUT count integer
   )

    RETURNS SETOF record AS

    $BODY$

Thanks

Upvotes: 0

Views: 591

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

As I don't understand what exactly you are trying I'm showing this as a starter.

To return a set of the array passed columns:

create or replace function facets(
    columns text[]
) returns setof record language plpgsql as $body$
begin
    return query execute format($$
        select %1$I, %2$I from t
    $$, columns[1], columns[2]);
end; $body$;

To retrieve it you will have to declare the return types:

select * from facets(array['a', 'b']) s(a text, b text);

Upvotes: 1

Related Questions