From: Dan Langille <dan@langille.org>
To: pgsql-sql@postgresql.org
Date: Sun, 28 Sep 2003 15:24:59 -0400 (EDT)
Message-ID: <20030928151910.S78282@laptop.unixathome.org> (raw)
I'm trying to create a function which returns a result set using a dynamic
query. The problem occurs when it compiles. I suspect it's my quoting,
but I'm not sure of the cause.
CREATE OR REPLACE FUNCTION LoginCounts(int) RETURNS SETOF
logincounts_record AS '
DECLARE
MaxDays ALIAS for $1;
r logincounts_record%rowtype;
i integer;
BEGIN
FOR i IN 1..MaxDays LOOP
EXECUTE ''
SELECT count(*)
INTO r
FROM users
WHERE lastlogin between current_date - interval \''' ||
quote_literal(i - 1) || '' days\'
AND current_date - interval \''' ||
quote_literal(i) || '' days\''';
RETURN NEXT r;
END LOOP;
RETURN;
END
'
LANGUAGE plpgsql;
# select * from LoginCounts(2);
WARNING: Error occurred while executing PL/pgSQL function logincounts
WARNING: line 9 at execute statement
ERROR: parser: parse error at or near "days" at character 151
thnks
--
Dan Langille - http://www.langille.org/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dan@langille.org
Subject: Re:
In-Reply-To: <20030928151910.S78282@laptop.unixathome.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox