Skip site navigation (1) Skip section navigation (2)

Re: A "cascade on delete" constraints deletes AFTER the source is gone??

From: "ON(dot)KG" <skyer(at)on(dot)kg>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: A "cascade on delete" constraints deletes AFTER the source is gone??
Date: 2004-12-20 13:31:59
Message-ID: 19826044359.20041220163159@on.kg (view raw or flat)
Thread:
Lists: pgsql-general
Hi All!

I need to make function which emulates table and returns setof columns
of this "table"

for example, i'm making query: "SELECT * FROM my_table(user_id)"

and function
CREATE OR REPLACE FUNCTION my_table (integer)
RETURNS setof text
AS '
  DECLARE
    check_date date;
    max_date date;
    r record;

  BEGIN
    max_date   := $2;
    check_date := $3;

    WHILE (check_date >= max_date) LOOP
      table_nam := ''table_''||to_char(check_date, ''YYYYMMDD'');
      
      FOR r IN EXECUTE ''SELECT COUNT(*) as cnt,
          AVG(amount) as avg_amount, SUM(amount) as sum_amount
        FROM ''||table_nam||''
        WHERE user_id = ''||$1||'' ''
      LOOP
        RETURN NEXT r.cnt || r.avg_amount || r.sum_amount;
      END LOOP;

      check_date := check_date - interval ''1 day'';
    END LOOP;
    
    RETURN;
  END;'
LANGUAGE 'plpgsql';


As a result i need to get a records, and will be able to use each
column from it

But my function doesn't work
What is wrong?

Thanx


In response to

Responses

pgsql-general by date

Next:From: Russ BrownDate: 2004-12-20 13:32:57
Subject: Re: PG8 final when
Previous:From: Együd CsabaDate: 2004-12-20 13:13:40
Subject: Re: PG8 final when

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group