2017-06-22 13:29 GMT+02:00 Gerard Matthews <gerardm...@gmail.com>: > It's under this page on the docs https://www.postgresql. > org/docs/9.1/static/ecpg-dynamic.html. > > It does not make it clear where this code can be executed. It's in the > documentation under chapter 33.5. Dynamic SQL. If you search in google for > postgres dynamic sql it's the first link. Here is link to google search. > https://www.google.co.za/search?q=postgresql+dynamic+sql&rlz=1C1CHZL_ > enZA685ZA685&oq=postgresql+dynamic+&aqs=chrome.0. > 0j69i57j0l4.8833j0j7&sourceid=chrome&ie=UTF-8 >
Unfortunately we have not control of Google. I understand so every body can be confused from this information. ecpg is old very specific feature of old databases. Few people uses it today. For you interesting page is https://www.postgresql.org/docs/9.6/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN but you need to use keyword "plpgsql" what is preferred language for stored procedures in Postgres. I don't know why Google is thinking so ecpg is more than plpgsql :(. Regards Pavel > > On Thu, 22 Jun 2017 at 12:37 Pavel Stehule <pavel.steh...@gmail.com> > wrote: > >> 2017-06-22 10:00 GMT+02:00 Gerard Matthews <gerardm...@gmail.com>: >> >>> 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; >>> $$; >>> >> >> where you see this code? >> >> It is part of embedded C, what is client side only code. You cannot to >> use embedded C on server side ever. >> >> DO command is designed for execution server side procedures - you can use >> PLpgSQL, PLPythonu or PLPerl languages. >> >> The functional example: >> >> DO $$ >> DECLARE stmt text; >> BEGIN >> stmt := 'CREATE TABLE ...()'; >> EXECUTE stmt; >> END; >> $$ LANGUAGE plpgsql; >> >> Related documentation: https://www.postgresql.org/ >> docs/9.6/static/plpgsql.html >> >> Maybe you are missing difference between client side and server side >> coding. Client side codes cannot to work on server side. >> >> Regards >> >> Pavel >> >> >>> [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. >>>>> >>>>
