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

1 Answer 1

Not a direct answer to your question but you should try the first_value window function. It works like this:

CREATE TABLE test (
    id SERIAL NOT NULL PRIMARY KEY,
    cat TEXT,
    value VARCHAR(2)
    date TIMESTAMP WITH TIME ZONE

);

Then, if you want the first item in each cat (category) you will query like that:

SELECT
    cat,
    first_value(date) OVER (PARTITION BY cat ORDER BY date)
FROM
    test;

or:

SELECT
    cat,
    first_value(date) OVER w
FROM
    test
WINDOW w AS (PARTITION BY cat ORDER BY date);
share|improve this answer
    
Interesting... That might probably work for my need. I will give it a try. Thanks –  Alexandre Neto Nov 27 at 21:16
    
Sorry, I don't think this apply to my use case. First_value is not an aggregation function, showing all records of the with a certain common value (your example cat) that is evaluated as being the first according to some order (your example date). My need is different. I need to, in the same select, agregate several columns by choosing the first not null value. That is, it should output a single record for each of value combinations in GROUP BY. –  Alexandre Neto Nov 28 at 17:16

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.