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@neosynapse.net
AIM: abraxan ICQ: 73804392
~~~~~~~~~~~~~~~~~~~~~~~~ There is no spoon. ~~~~~~~~~~~~~~~~~~~~~~~~
