Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Full question re-write

I'm looking a First() aggregate function.

Here I found something that almost works:

CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

The problem is that when a varchar(n) columns passes trough the first() function, it's converted into simple varchar (without a size). Trying to return the query in a function as RETURNS SETOF anyelement, I get the following error:

ERROR: structure of query does not match function result type Estado de SQL:42804 Detalhe:Returned type character varying does not match expected type character varying(40) in column 2. Contexto:PL/pgSQL function vsr_table_at_time(anyelement,timestamp without time zone) line 31 at RETURN QUERY

In the same wiki page there is a link to a C Version of the function that would replace the above. I don't know how to install it, but I wonder if this version could solve my problem.

Meanwhile, is there a way I can change the above function so it return the exact same type of the input column?

share|improve this question
1  
there is a function called pg_typeof(). It is not really documented, but should help you. See this SO question where the return type is casted using a dynamic SQL: stackoverflow.com/questions/10571741/… –  cha May 29 at 22:35
    
This is how you create dynamic sql in postgresql: postgresql.org/docs/9.1/static/ecpg-dynamic.html Unfortunately I can't test your function. It does not work in SQL Fiddle, and I do not have a test server to play with at the moment –  cha May 29 at 22:40
    
uhmm... so in your opinion the solution must be reading the column type and casting it correctly using dynamic SQL, but where should I do the Dynamic SQL. I really don't understand how the two above functions work. –  Alexandre Neto May 29 at 23:14
    
As I said I do not have a test server. I guess you can replace SELECT $1; with EXEC SQL BEGIN DECLARE SECTION; const char *stmt = "SELECT CAST(" + $1 + " AS" + pg_typeof($1)+")"; EXEC SQL END DECLARE SECTION; EXEC SQL EXECUTE IMMEDIATE :stmt; –  cha May 29 at 23:32
    
I gave it a try, but pg_typeof returns the column type, but not it's size. So if I input a varchar(20) returns a simple varchar. And this is the main problem. I have been working on getting the columns type already, if you say that casting is the way to go, I will continue from there. –  Alexandre Neto May 30 at 8:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.