pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcannot create function that uses variable table name
6+ messages / 5 participants
[nested] [flat]
* cannot create function that uses variable table name
@ 2003-01-17 03:32 Matthew Nuzum <cobalt@bearfruit.org>
2003-01-17 04:42 ` Re: cannot create function that uses variable table name chester c young <chestercyoung@yahoo.com>
2003-01-17 04:43 ` Re: cannot create function that uses variable table name Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-17 09:48 ` Re: cannot create function that uses variable table name D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 3 replies; 6+ messages in thread
From: Matthew Nuzum @ 2003-01-17 03:32 UTC (permalink / raw)
To: pgsql-sql
I have a number of tables in my database that use the concept of
“display order”, which is a field that can be used in an order by clause
to dictate what order the results should come out in.
I thought I would be crafty and devise a function that would always
return the highest numbered item in the table. But it doesn’t work. It
always gives me a parse error at $1. Here’s the function:
CREATE OR REPLACE FUNCTION get_last_dsply_order(
varchar, -- tablename
varchar, -- id_col_name
varchar) -- where_item
RETURNS integer AS '
DECLARE total_items integer;
tablename ALIAS FOR $1;
id_col_name ALIAS FOR $2;
where_item ALIAS FOR $3;
BEGIN
SELECT INTO total_items count(*) FROM tablename WHERE id_col_name
= where_item;
RETURN total_items;
END;
' LANGUAGE 'plpgsql';
Here’s some sample data so that you can better see what I’m doing:
Fileid| accountid | filename | dsply_order
==============================================
1| account1 | My File | 1
2| account1 | Another file | 2
3| account1 | YA File | 3
4| account2 | Hello world | 1
5| account2 | Hi again | 2
6| account3 | Good bye | 3
7| account4 | Mom | 2
8| account4 | Dad | 1
=============================================
Therefore you would want to see the last item number used by account2 so
that you can add a new item to the end of the list. You might do
something like this:
INSERT INTO files (accountid, filename, dsply_order) VALUES
(‘account2’,’Testing’,get_last_dsply_order(‘files’,’accountid’,’account2
’));
BTW, it will have a complementary trigger assigned to each table that
upon delete will shift all the items up 1 to fill in the gap left by the
deleted item. Therefore the count() of the items in the table should
also match the highest numbered item.
--
Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: cannot create function that uses variable table name
2003-01-17 03:32 cannot create function that uses variable table name Matthew Nuzum <cobalt@bearfruit.org>
@ 2003-01-17 04:42 ` chester c young <chestercyoung@yahoo.com>
2 siblings, 0 replies; 6+ messages in thread
From: chester c young @ 2003-01-17 04:42 UTC (permalink / raw)
To: Matthew Nuzum <cobalt@bearfruit.org>; +Cc: pgsql-sql
--- Matthew Nuzum <cobalt@bearfruit.org> wrote:
> I thought I would be crafty and devise a function that would always
> return the highest numbered item in the table. But it doesnt work.
> It always gives me a parse error at $1. Heres the function:
build the query as a string and execute it.
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: cannot create function that uses variable table name
2003-01-17 03:32 cannot create function that uses variable table name Matthew Nuzum <cobalt@bearfruit.org>
@ 2003-01-17 04:43 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2003-01-17 04:43 UTC (permalink / raw)
To: Matthew Nuzum <cobalt@bearfruit.org>; +Cc: pgsql-sql
On Thu, 16 Jan 2003, Matthew Nuzum wrote:
> I have a number of tables in my database that use the concept of
> display order, which is a field that can be used in an order by clause
> to dictate what order the results should come out in.
>
> I thought I would be crafty and devise a function that would always
> return the highest numbered item in the table. But it doesnt work. It
> always gives me a parse error at $1. Heres the function:
>
> CREATE OR REPLACE FUNCTION get_last_dsply_order(
> varchar, -- tablename
> varchar, -- id_col_name
> varchar) -- where_item
> RETURNS integer AS '
> DECLARE total_items integer;
> tablename ALIAS FOR $1;
> id_col_name ALIAS FOR $2;
> where_item ALIAS FOR $3;
> BEGIN
> SELECT INTO total_items count(*) FROM tablename WHERE id_col_name
> = where_item;
> RETURN total_items;
> END;
> ' LANGUAGE 'plpgsql';
You'll need to look into EXECUTE. You also are going to have to
watch out for concurrency issues since two transactions calling
this function at the same time for the same args are likely to
give incorrect results.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: cannot create function that uses variable table name
2003-01-17 03:32 cannot create function that uses variable table name Matthew Nuzum <cobalt@bearfruit.org>
@ 2003-01-17 09:48 ` D'Arcy J.M. Cain <darcy@druid.net>
2 siblings, 0 replies; 6+ messages in thread
From: D'Arcy J.M. Cain @ 2003-01-17 09:48 UTC (permalink / raw)
To: Matthew Nuzum <cobalt@bearfruit.org>; pgsql-sql
On Thursday 16 January 2003 22:32, Matthew Nuzum wrote:
> I have a number of tables in my database that use the concept of
> “display order”, which is a field that can be used in an order by clause
> to dictate what order the results should come out in.
>
> I thought I would be crafty and devise a function that would always
> return the highest numbered item in the table. But it doesn’t work. It
> always gives me a parse error at $1. Here’s the function:
I may be wrong but aren't you trying to do something like this?
INSERT INTO files (accountid, filename, dsply_order)
VALUES ('account2', 'Testing',
(SELECT COALESCE(MAX(dsply_order), 0) + 1 FROM files
WHERE accountid = 'account2'));
Alternatively, assuming that fileid is a serial number, why not just use that
in your order by clause. I assume that you want something like this.
SELECT * FROM files WHERE accountid = 'account2' ORDER BY dsply_order;
This should give you exactly the same result:
SELECT * FROM files WHERE accountid = 'account2' ORDER BY fileid.
It all depends on what problem exactly you are trying to solve of course.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: cannot create function that uses variable table name
@ 2003-01-20 03:17 Beth <bethg@cybernamix.com>
2003-01-24 00:25 ` Re: cannot create function that uses variable table name Beth <bethg@cybernamix.com>
0 siblings, 1 reply; 6+ messages in thread
From: Beth @ 2003-01-20 03:17 UTC (permalink / raw)
To: pgsql-sql
As per Matthew Nuzum's post
My query is very similar...
I need sql functions to update the database. If I specify the filename
etc they work. BUT that leads to 6 functions which are exactly the same
apart from the file they update.
1) why can't I use a variable name and
2) could someone please point me towards some examples of EXECUTE if
thats the only way to do it?
my example is:
CREATE FUNCTION retire(varchar, varchar, int4) returns int4 AS'
BEGIN
UPDATE $1 SET "Retired" = 'true' WHERE $2 = $3;
SELECT (whatever to return the int4);
END;'
Language 'plpgsql';
which has: parse error at or near "true"
($1 is the filename, $2 is the fieldname, $3 is the fieldvalue and
"Retired" is a boolean field in each of the files)
Thanks
Beth
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: cannot create function that uses variable table name
2003-01-20 03:17 Re: cannot create function that uses variable table name Beth <bethg@cybernamix.com>
@ 2003-01-24 00:25 ` Beth <bethg@cybernamix.com>
0 siblings, 0 replies; 6+ messages in thread
From: Beth @ 2003-01-24 00:25 UTC (permalink / raw)
To: ddurst@larubber.com; pgsql-sql
Thanks for your reply David...
1)The "" round Retired are to label the column/field 'Retired' rather
than 'retired' (someone else created the database with Upper case
titles!)
2) Your code is correct.. cept that single quotes have to be escaped(?!)
so the following will do the trick when updating text fields...
CREATE FUNCTION temp(text,text,int4) RETURNS integer AS '
DECLARE
update_table ALIAS FOR $1;
update_field ALIAS FOR $2;
update_id ALIAS FOR $3;
BEGIN
EXECUTE ''UPDATE ''|| quote_ident(update_table) || '' SET "Retired" =
''''true'''' WHERE '' || quote_ident(update_field) || '' = '' ||
quote_literal(update_id);
RETURN update_id;
END;
' language 'plpgsql';
which creates...
and: select temp('TableName', 'TableID', 20);
returns 20.
On Fri, 2003-01-24 at 13:13, David Durst wrote:
> > I need sql functions to update the database. If I specify the filename
> > etc they work. BUT that leads to 6 functions which are exactly the same
> > apart from the file they update.
> >
> > 1) why can't I use a variable name and
> > 2) could someone please point me towards some examples of EXECUTE if
> > thats the only way to do it?
> >
> >
> > my example is:
> >
>
> This should work
>
> CREATE FUNCTION retire(varchar, varchar, int4) returns int4 AS'
> DECLARE
> varone ALIAS FOR $1;
> vartwo ALIAS FOR $2;
> varthr ALIAS FOR $3;
> BEGIN
> UPDATE varone SET "Retired" = 'true' WHERE vartwo = varthr;
> SELECT (whatever to return the int4);
> END;'
> Language 'plpgsql';
>
>
> And I am not sure of the purpose for the "" around Retired
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-01-24 00:25 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-17 03:32 cannot create function that uses variable table name Matthew Nuzum <cobalt@bearfruit.org>
2003-01-17 04:42 ` chester c young <chestercyoung@yahoo.com>
2003-01-17 04:43 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-17 09:48 ` D'Arcy J.M. Cain <darcy@druid.net>
2003-01-20 03:17 Re: cannot create function that uses variable table name Beth <bethg@cybernamix.com>
2003-01-24 00:25 ` Beth <bethg@cybernamix.com>
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