Here's an example. this code does not exec for me, I get syntax error. I have PG version 9.5
DO $$ BEGIN EXEC SQL BEGIN DECLARE SECTION; const char *stmt = "CREATE TABLE test1 (...);"; EXEC SQL END DECLARE SECTION; EXEC SQL EXECUTE IMMEDIATE :stmt; END; $$; [image: image.png] On Thu, 22 Jun 2017 at 09:21 Gerard Matthews <gerardm...@gmail.com> wrote: > Hi Everyone, > > Firstly I would like to thank everyone for your feedback. Secondly I would > like to apologise, I had spent some time trying to write some dynamic SQL > in postgres yesterday and got extremely frustrated at the the time I > wasted. I realize this a community and I'm sorry for not being more > constructive in my comment. > > The reason for my frustration is this has happened before; where I look > the examples and think I know how to use it only to run into syntax errors. > I have tried the specific dynamic SQL examples inside script blocks and I > get syntax errors. If it only works in functions it would be good to know > that. > > If it's not that then I wonder if perhaps the problem is PG Admin. > > Often the way I learn with a new language is by trying to do something > that I need. Hence I hit the docs at the point that I think will help me. > If this happens with other developers perhaps it would be helpful for the > examples to list where the specific language feature can be used. > > I am not the only one as my colleagues have run into exactly the same > thing. > > Hopefully this input is a little more constructive and again thank you all > for taking the time to respond. > > On Thu, 22 Jun 2017 at 02:47 David G. Johnston <david.g.johns...@gmail.com> > wrote: > >> >>> 2017-06-21 10:39 GMT+02:00 <gerardm...@gmail.com>: >> >>> >> >>> Your documentation although it seems straight forward actually never >> >>> works. >> >> Most of it is not written as self-contained examples so this is not >> surprising. >> >> >>> Please explain limitations or where the script can actually be >> executed. >> >> That would be the responsibility of chapters 36 and 41.2 (and maybe >> some others); chapters prior to the one you are complaining about. >> Again, this isn't a cookbook format where every section and example is >> self-contained. This requires the reader to adopt their own >> techniques for actually starting with functioning code and keeping it >> functioning as new capabilities are introduced. For better and worse >> I don't foresee any volunteering significant time to change the style >> of the documentation - particularly without a large volume of specific >> complaints and/or suggestions to work from. >> >> >>> As a beginner starting out in postgre you can never rely on the >> >>> documentation because the same thing happens, you copy the code and >> make >> >>> your modifications and you get syntax error. >> >> Which means that some more fundamental aspect of the capability you >> are trying to learn hasn't yet been figured out and so that code you >> are writing is not operating in the context that the documentation >> assumes it is. You need to go back earlier in the >> process/documentation and get the fundamentals out of the way. >> >> >>> Clearly define the limitations of your language so that developers >> >>> don't >> >>> waste their time. >> >> Either there is a distinct lack of others encountering the same >> problems or they avail themselves of other solutions. In any case the >> authors of the documentation cannot foresee every confusion or problem >> that may arise. That's part of why the mailing lists exist. Ideally >> you'd come here, get un-stumped, look back at why you got stumped in >> the first place, and suggest documentation improvements that would >> help prevent the next person from being stumped in a similar matter. >> That flow would, IMO, be in the true spirit of this open source >> community. >> >> >>> Coming from ms sql quite frankly I would never recommend postgress. >> The >> >>> barrior to entry into actualy writing code is too great. >> >> --file: create-functions.sql >> --might not work as-is but the structural components you require are here. >> CREATE FUNCTION remove_all_users() RETURNS SETOF bigint AS $$ >> DELETE FROM users RETURNING user_id; >> $$ LANGUAGE SQL; >> >> CREATE FUNCTION do_it() RETURNS void AS $$ >> BEGIN >> RAISE NOTICE 'Performing User Removal'; >> PERFORM remove_all_users(); -- using perform since we don't care about >> returned user_id's >> END; >> $$ LANGUAGE plpgsql; >> >> $psql >> >\i create-functions.sql >> #now the two functions exist on the server >> >SELECT do_it(); >> %NOTICE: Performing User Removal >> do_it >> ------- >> >> --this would be roughly equivalent but "DO" won't return a result >> which SELECT do_it() does. >> --still in psql... >> DO $$ >> BEGIN >> PERFORM remove_all_users(); >> END; >> $$; --implied pl/pgsql language >> >> I cannot speak to learning MS SQL compared to PostgreSQL; but in some >> ways having existing, but difference, experience hurts since you are >> apt to make assumptions about how things should work that are not >> true. >> >> Your welcome to your venting but all I see here is a specific case of >> learning having gone into spiral. The community here is great at >> helping people get themselves out these kinds of spirals. That the >> documentation cannot do so all by itself is not a failing of the >> documentation or its authors. >> >> David J. >> >
