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

Question

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

Just as an example to make it clear, how would the select be to return the count for two columns keyword and search_fields? One count for each column? What is in the set your example function returns? — Clodoaldo Neto
– Clodoaldo Neto, Commented Apr 24, 2013 at 13:10
No it will always count the id(primary) but the search condition will try to match with several fields of the table and I need to pass those fields as a list beside all other parameters as those fields varies depending on relation. — Muhit
– Muhit, Commented Apr 24, 2013 at 13:31

Clodoaldo Neto · Accepted Answer · 2013-04-24 13:20:40Z

1

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);

answered Apr 24, 2013 at 13:20

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related