From: | "Steven D(dot) Arnold" <stevena(at)neosynapse(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | strange stored procedure problem |
Date: | 2001-05-24 20:16:19 |
Message-ID: | 5.0.2.1.2.20010524160504.0683e008@phear.dementian.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I am writing stored procedures for a database application. Many times my
stored procedures call other stored procedures for various reasons. I
wrote a test procedure to test my other procedures. It looks like this:
CREATE FUNCTION test_postgres() RETURNS INTEGER AS '
spi_exec "SELECT create_user_rsrc('''', '''', ''thoth'', '''',
'''', ''Steven'', ''Arnold'', 0, ''*****'', 0) AS
i_user_id"
spi_exec "SELECT create_role_rsrc('''', '''', ''generic_role'',
'''', '''') AS i_role_id"
spi_exec "SELECT add_user_to_role('''', '''', 68, 69) AS i_rel_id"
return 1
' LANGUAGE 'pltcl';
When I call this function, my stored procedures run and claim to have
inserted a new user and role into the appropriate tables. However, when I
check the tables, I see zero rows. I put the actual insert SQL into a
string and printed it from the procedure to make sure I really know what
SQL the procedure was executing; it looked good. When I executed the same
SQL string from the command-line, it inserted fine. But calling the above
stored procedure just didn't seem to insert the rows!
When I take a snippet of SQL from the procedure above and run it manually,
it works exactly as expected:
mydb=> SELECT create_user_rsrc('', '', 'thoth', '',
mydb(> '', 'Steven', 'Arnold', 0, '*****', 0);
NOTICE: v_name is 'thoth' and v_table is 'users'
NOTICE: about to insert into table users
NOTICE: I just theoretically created a user with ID 68
create_user_rsrc
------------------
68
(1 row)
mydb=> select * from users;
rsrc_id | name | english_name | rsrc_type_id | description | [...]
---------+-------+--------------+--------------+-------------+ [...]
68 | thoth | | 6 | | [...]
(1 row)
I am mystified by this problem. Any idea what's up?
------------------------------------------------------------------------
Steven D. Arnold stevena(at)neosynapse(dot)net
AIM: abraxan ICQ: 73804392
~~~~~~~~~~~~~~~~~~~~~~~~ There is no spoon. ~~~~~~~~~~~~~~~~~~~~~~~~
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-05-24 21:39:24 | Re: array bad behavior? |
Previous Message | Linh Luong | 2001-05-24 19:58:11 | Column name |