PostgreSQL: return select count(*) from old_ids;
- by Alexander Farber
Hello,
please help me with 1 more PL/pgSQL question.
I have a PHP-script run as daily cronjob and deleting old records from 1 main table and few further tables referencing its "id" column:
create or replace function quincytrack_clean()
returns integer as $BODY$
begin
create temp table old_ids
(id varchar(20)) on commit drop;
insert into old_ids
select id from quincytrack
where age(QDATETIME) > interval '30 days';
delete from hide_id where id in
(select id from old_ids);
delete from related_mks where id in
(select id from old_ids);
delete from related_cl where id in
(select id from old_ids);
delete from related_comment where id in
(select id from old_ids);
delete from quincytrack where id in
(select id from old_ids);
return select count(*) from old_ids;
end;
$BODY$ language plpgsql;
And here is how I call it from the PHP script:
$sth = $pg->prepare('select quincytrack_clean()');
$sth->execute();
if ($row = $sth->fetch(PDO::FETCH_ASSOC))
printf("removed %u old rows\n", $row['count']);
Why do I get the following error?
SQLSTATE[42601]: Syntax error: 7
ERROR: syntax error at or near "select" at character 9
QUERY: SELECT select count(*) from old_ids
CONTEXT: SQL statement in PL/PgSQL function
"quincytrack_clean" near line 23
Thank you! Alex
