CODEWITHSUNDEEP
sqlpostgresqlplpgsql
St.Antario
St.Antario

Reputation: 27385

How can I retrieve a column value of a specific row

I'm using PostgreSQL 9.3.

The table partner.partner_statistic contains the following columns:

  id     reg_count
serial    integer

I wrote the function convert(integer):

CREATE FUNCTION convert(d integer) RETURNS integer AS $$
BEGIN
--Do something and return integer result
END
$$ LANGUAGE plpgsql;

And now I need to write a function returned array of integers as follows:

 CREATE FUNCTION res() RETURNS integer[] AS $$
    << outerblock >>
    DECLARE
        arr integer[]; --That array of integers I need to fill in depends on the result of query
        r partner.partner_statistic%rowtype;
        table_name varchar DEFAULT 'partner.partner_statistic';
    BEGIN
        FOR r IN 
        SELECT * FROM partner.partner_statistic offset 0 limit 100
        LOOP
        --
        -- I need to add convert(r[reg_count]) to arr where r[id] = 0 (mod 5)
        --
        -- How can I do that?
        END LOOP;
        RETURN;
    END;
    $$ LANGUAGE plpgsql;

Upvotes: 0

Views: 34

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324465

You don't need (and shouldn't use) PL/PgSQL loops for this. Just use an aggregate. I'm kind of guessing about what you mean by "where r[id] = 0 (mod 5) but I'm assuming you mean "where id is evenly divisible by 5". (Note that this is NOT the same thing as "every fifth row" because generated IDs have gaps).

Something like:

SELECT array_agg(r.reg_count) 
FROM partner.partner_statistic
WHERE id % 5 = 0
LIMIT 100

probably meets your needs.

If you want to return the value, use RETURN QUERY SELECT ... or preferably use a simple sql language function.

If you want a dynamic table name, use:

  RETURN QUERY EXECUTE format('
    SELECT array_agg(r.reg_count) 
    FROM %I
    WHERE id % 5 = 0
    LIMIT 100', table_name::regclass);

Upvotes: 3

Related Questions