The main reason I wanted to use a prepared statement was to avoid the possibility of injection… But I found a sort of workaround on-line.
I put it in stored function but I think I could create won “prepared statements” by doing the same thing using teh format function
Here is the stored function:
CREATE OR REPLACE FUNCTION NewUser(username varchar(63), thepassword varchar(63))
RETURNS OID
LANGUAGE plpgsql
AS $NewUser$
BEGIN
EXECUTE format('CREATE USER %I IN ROLE user_basic CONNECTION LIMIT 1 PASSWORD %L', username, thepassword);
EXECUTE format('GRANT CONNECT On DATABASE the_db To %I', username);
RETURN (SELECT OID FROM pg_roles WHERE rolname = username);
END;
$NewUser$;
Then I call it as:
Dim RS as RecordSet = DB.SQLSelect("Select NewUser( 'Training', 'test')")
%I means that string is formatted as a Postgres Identifier and %L means it is a quoted string literal.
So that is where the injection prevention comes from.
Anyway so now I know how to do this DDL stuff.
-Karen