pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/PGSQL Function problem.
6+ messages / 6 participants
[nested] [flat]
* PL/PGSQL Function problem.
@ 2000-08-15 17:39 Dirk Elmendorf <delmendo@rackspace.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dirk Elmendorf @ 2000-08-15 17:39 UTC (permalink / raw)
To: pgsql-sql
RedHat 6.2 /Postgres 6.53
I'm still very new at PL/PGSQL but I have looked thru all the
documentation I could find before sending this in.
I have a function that I would like to be able to apply to multiple
tables without having to duplicate the code. I would like to be able
to pass in the table name I would like to apply the function to. I
cannot seem to get this to work. Below is what I have so far. I
would like to be able to have
computers_equal(comp_one,comp_two,comp_table_one,comp_table_two);
I cannot seem to find a way to pass in a text string to be used in
the select into statements.
Anyone got any ideas on how I fix this other that writing a function
for all the permutations (ordered_parts vs used_parts , ordered_parts
vs new_parts, used_parts vs ordered_parts, new_parts vs
ordered_parts,new_parts vs used_parts, used_parts vs ordered_parts)
Any advice would be appreciated.
create table ordered_parts
(
computer_number int4,
part_number int4
);
create table used_parts
(
computer_number int4,
part_number int4
);
create tabel new_parts
(
computer_number int4,
part_number int4
);
insert into ordered_parts values(1,401);
insert into ordered_parts values(1,402);
insert into used_parts values(2,401);
insert into used_parts values(2,402);
insert into used_parts values(3,401);
insert into used_parts values(3,403);
insert into new_parts values(4,401);
insert into new_parts values(4,402);
insert into new_parts values(5,401);
insert into new_parts values(5,403);
CREATE FUNCTION computers_equal(int4 , int4) RETURNS bool AS '
DECLARE
true CONSTANT bool DEFAULT ''t'';
false CONSTANT bool DEFAULT ''f'';
parts_list RECORD;
BEGIN
SELECT INTO parts_list part_number,part_label from
ordered_parts where computer_number = $1 except select
part_number,part_label from used_parts where computer_number= $2;
IF NOT FOUND THEN
-- now test the reverse
SELECT INTO parts_list
part_number,part_label from used_parts where computer_number = $2
except select part_number,part_label from ordered_parts where
computer_number= $1;
IF NOT FOUND THEN
RETURN true;
ELSE
RETURN false;
END IF;
ELSE
RETURN false;
END IF;
END;
' LANGUAGE 'plpgsql';
--This will list all the comptuers that match
select distinct on computer_number computer_number from used_parts
where computers_equal(1,computer_number) and computer_number!=1;
/*
--this doesn't work
CREATE FUNCTION computers_equal(int4 , int4,text,text) RETURNS bool AS '
DECLARE
true CONSTANT bool DEFAULT ''t'';
false CONSTANT bool DEFAULT ''f'';
parts_list RECORD;
BEGIN
SELECT INTO parts_list part_number,part_label from $3
where computer_number = $1 except select part_number,part_label from
$4 where computer_number= $2;
IF NOT FOUND THEN
-- now test the reverse
SELECT INTO parts_list
part_number,part_label from $4 where computer_number = $2 except
select part_number,part_label from $3 where computer_number= $1;
IF NOT FOUND THEN
RETURN true;
ELSE
RETURN false;
END IF;
ELSE
RETURN false;
END IF;
END;
' LANGUAGE 'plpgsql';
select distinct on computer_number computer_number from used_parts
where computers_equal(1,computer_number,ordered_parts,used_parts) and
computer_number!=1;
*/
--
______________________________________________________________________
Dirk Elmendorf, CTE Main: 210-892-4000
Rackspace Managed Hosting
Weston Center Fax: 210-892-4329
112 East Pecan, Suite 600 Email:dirk@rackspace.com
San Antonio, TX 78205 <http://www.rackspace.com;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PL/PGSQL Function problem.
@ 2000-08-15 18:07 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Dirk Elmendorf <delmendo@rackspace.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2000-08-15 18:07 UTC (permalink / raw)
To: Dirk Elmendorf <delmendo@rackspace.com>; +Cc: pgsql-sql
Actually, you can't do too much about it in PL/PGSQL,
because IIRC there isn't a way right now to do that.
I don't remember when PL/TCL came into existance (don't
know TCL), but you'd be able to do it in that, and
you can also do it from SPI in a C function.
And finally, the obligatory upgrade message... Upgrade
to 7.0.2 if you can, it's nice and stays crunchy in
milk. :)
Stephan Szabo
sszabo@bigpanda.com
On Tue, 15 Aug 2000, Dirk Elmendorf wrote:
> RedHat 6.2 /Postgres 6.53
> I'm still very new at PL/PGSQL but I have looked thru all the
> documentation I could find before sending this in.
>
>
> I have a function that I would like to be able to apply to multiple
> tables without having to duplicate the code. I would like to be able
> to pass in the table name I would like to apply the function to. I
> cannot seem to get this to work. Below is what I have so far. I
> would like to be able to have
>
> computers_equal(comp_one,comp_two,comp_table_one,comp_table_two);
>
> I cannot seem to find a way to pass in a text string to be used in
> the select into statements.
>
> Anyone got any ideas on how I fix this other that writing a function
> for all the permutations (ordered_parts vs used_parts , ordered_parts
> vs new_parts, used_parts vs ordered_parts, new_parts vs
> ordered_parts,new_parts vs used_parts, used_parts vs ordered_parts)
>
> Any advice would be appreciated.
^ permalink raw reply [nested|flat] 6+ messages in thread
* PL/pgSQL Function Problem
@ 2004-09-09 17:49 the inquirer <listquestions@yahoo.com>
0 siblings, 3 replies; 6+ messages in thread
From: the inquirer @ 2004-09-09 17:49 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-novice@postgresql.org; pgsql-sql
I am trying to create a function that creates a user
and adds a row to a table. It produces no warnings or
errors when I create the function but when I attempt
to execute it I get a syntax error. I do not
understand why this is happening. Any help would be
greatly appreciated.
SELECT create_author( 'name', 'username', 'password'
);
ERROR: syntax error at or near "$1" at character 14
CONTEXT: PL/pgSQL function "create_author" line 7 at
SQL statement
Here is the code:
CREATE OR REPLACE FUNCTION create_author (
VARCHAR(32), VARCHAR(32), VARCHAR(32) )
RETURNS INTEGER AS '
DECLARE
name_ ALIAS FOR $1;
username_ ALIAS FOR $2;
password_ ALIAS FOR $3;
authorid_ INTEGER;
BEGIN
CREATE USER username_ WITH ENCRYPTED PASSWORD
password_ IN GROUP authors;
INSERT INTO Authors
( Name, Username )
VALUES
( $1, $2 );
SELECT Max( AuthorID ) INTO authorid_ FROM Authors;
RETURN authorid_;
END;
' LANGUAGE 'plpgsql'
SECURITY INVOKER
RETURNS NULL ON NULL INPUT;
__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PL/pgSQL Function Problem
@ 2004-09-11 06:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: the inquirer <listquestions@yahoo.com>
2 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2004-09-11 06:03 UTC (permalink / raw)
To: the inquirer <listquestions@yahoo.com>; +Cc: pgsql-general@postgresql.org; pgsql-novice@postgresql.org; pgsql-sql
the inquirer <listquestions@yahoo.com> writes:
> I am trying to create a function that creates a user
> and adds a row to a table. It produces no warnings or
> errors when I create the function but when I attempt
> to execute it I get a syntax error. I do not
> understand why this is happening.
> CREATE OR REPLACE FUNCTION create_author (
> VARCHAR(32), VARCHAR(32), VARCHAR(32) )
> RETURNS INTEGER AS '
> DECLARE
> name_ ALIAS FOR $1;
> username_ ALIAS FOR $2;
> password_ ALIAS FOR $3;
> authorid_ INTEGER;
> BEGIN
> CREATE USER username_ WITH ENCRYPTED PASSWORD
> password_ IN GROUP authors;
Utility statements (which is to say anything except SELECT/INSERT/
UPDATE/DELETE) generally don't cope with parameters. The above won't
work because it's trying to substitute parameters for username_ and
password_ in the CREATE USER utility statement. You could make it work
by constructing the CREATE USER command as a string and then EXECUTE'ing
it.
(I agree this ain't ideal, but it's where we're at...)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] PL/pgSQL Function Problem
@ 2004-09-11 06:22 Michalis Kabrianis <mk@interzone.gr>
parent: the inquirer <listquestions@yahoo.com>
2 siblings, 0 replies; 6+ messages in thread
From: Michalis Kabrianis @ 2004-09-11 06:22 UTC (permalink / raw)
To: the inquirer <listquestions@yahoo.com>; +Cc: pgsql-general@postgresql.org; pgsql-novice@postgresql.org; pgsql-sql
the inquirer wrote:
> I am trying to create a function that creates a user
> and adds a row to a table. It produces no warnings or
> errors when I create the function but when I attempt
> to execute it I get a syntax error. I do not
> understand why this is happening. Any help would be
> greatly appreciated.
>
> SELECT create_author( 'name', 'username', 'password'
> );
>
> ERROR: syntax error at or near "$1" at character 14
> CONTEXT: PL/pgSQL function "create_author" line 7 at
> SQL statement
>
> Here is the code:
>
> CREATE OR REPLACE FUNCTION create_author (
> VARCHAR(32), VARCHAR(32), VARCHAR(32) )
> RETURNS INTEGER AS '
> DECLARE
> name_ ALIAS FOR $1;
> username_ ALIAS FOR $2;
> password_ ALIAS FOR $3;
> authorid_ INTEGER;
> BEGIN
> CREATE USER username_ WITH ENCRYPTED PASSWORD
> password_ IN GROUP authors;
>
> INSERT INTO Authors
> ( Name, Username )
> VALUES
> ( $1, $2 );
> SELECT Max( AuthorID ) INTO authorid_ FROM Authors;
>
> RETURN authorid_;
>
> END;
> ' LANGUAGE 'plpgsql'
> SECURITY INVOKER
> RETURNS NULL ON NULL INPUT;
>
>
>
As Tom Lane said before me, use EXECUTE.
I have that on a similar project
CREATE FUNCTION s_user() RETURNS "trigger"
AS '
DECLARE
uname text;
BEGIN
uname := ''s'' || NEW.code::character varying;
EXECUTE ''CREATE USER ''||uname||'' WITH ENCRYPTED PASSWORD
''''pass'''' NOCREATEDB NOCREATEUSER IN GROUP salesmen;'';
RETURN NEW;
END
'
LANGUAGE plpgsql SECURITY DEFINER;
It's obviously is a trigger on an insert on some table, and creates the
username based on that tables' primary key. It also sets a standard
password, to be canged by the user.
I use it with SECURITY DEFINER because users that use that piece of code
are ordinary users and don't have the right to create users in any other
way.
Michalis
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PL/pgSQL Function Problem
@ 2004-09-11 13:55 Doug McNaught <doug@mcnaught.org>
parent: the inquirer <listquestions@yahoo.com>
2 siblings, 0 replies; 6+ messages in thread
From: Doug McNaught @ 2004-09-11 13:55 UTC (permalink / raw)
To: the inquirer <listquestions@yahoo.com>; +Cc: pgsql-general@postgresql.org; pgsql-novice@postgresql.org; pgsql-sql
the inquirer <listquestions@yahoo.com> writes:
> ERROR: syntax error at or near "$1" at character 14
> CONTEXT: PL/pgSQL function "create_author" line 7 at
> SQL statement
PL/pgSQL errors are sometimes obscure. I'll try to comment on what
looks wrong to me...
> CREATE OR REPLACE FUNCTION create_author (
> VARCHAR(32), VARCHAR(32), VARCHAR(32) )
> RETURNS INTEGER AS '
> DECLARE
> name_ ALIAS FOR $1;
> username_ ALIAS FOR $2;
> password_ ALIAS FOR $3;
> authorid_ INTEGER;
> BEGIN
> CREATE USER username_ WITH ENCRYPTED PASSWORD
> password_ IN GROUP authors;
I can't tell you why, but my suspicion is that you need to use EXECUTE
in order to do CREATE USER in a function. Try that.
> INSERT INTO Authors
Be aware that, unless you double-quote the identifier, "Author" will
be folded to "author" by the SQL parser. This may also be causing a
problem--you didn't post your schema.
> ( Name, Username )
> VALUES
> ( $1, $2 );
> SELECT Max( AuthorID ) INTO authorid_ FROM Authors;
This is a potential race, depending on your transaction isolation
level. It would be better to find out the sequence name for your
SERIAL column and user currval('<that sequence>') as the return value.
Hope this helps!
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-09-11 13:55 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-15 17:39 PL/PGSQL Function problem. Dirk Elmendorf <delmendo@rackspace.com>
2000-08-15 18:07 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2004-09-09 17:49 PL/pgSQL Function Problem the inquirer <listquestions@yahoo.com>
2004-09-11 06:03 ` Re: PL/pgSQL Function Problem Tom Lane <tgl@sss.pgh.pa.us>
2004-09-11 06:22 ` Re: [SQL] PL/pgSQL Function Problem Michalis Kabrianis <mk@interzone.gr>
2004-09-11 13:55 ` Re: PL/pgSQL Function Problem Doug McNaught <doug@mcnaught.org>
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