SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[12.0.191 Pro] - Anonymous blocks in $$...$$ macro(Postgres)

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[12.0.191 Pro] - Anonymous blocks in $$...$$ macro(Postgres)
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [12.0.191 Pro] - Anonymous blocks in $$...$$ macro(Postgres) Reply with quote
In SA, custom macros are defined by encapsulating the code in a pair of double $ signs. The anonymous blocks in Postgres also use a pair of double $$ signs. My question is how do I combine them? I want to execute some code and then put its result into the editor.
Thu Jan 13, 2022 7:47 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
Within the snippets code $ symbols can be escaped using trailing ^

Code:
DO $^$^
   -- macro invoked here
   PERFORM $OBJECT$
$^$^ LANGUAGE plpgsql;   

Thu Jan 13, 2022 1:01 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
That works. Well, sort of. However, what I wanted to do, does not. Processing the data is much easier in the anonymous block using variables and stuff, but I cannot return data from there. Therefore I thought I'd put that into a temporary table, and then select it from outside of the anonymous block. But that throws another error message stating that
Quote:

SQL State: 42601, ERROR: cannot insert multiple commands into a prepared statement...


So I tried to move the statements into separate $$...$$ macros
Code:

$PROMPT(surround,Surround with what? :),,Surrounder, (...) [...] [#...])$$$
DO
$^$^
DECLARE
    "lv_command" text;
    "lv_curselesc" text;
    "lv_curselesc_ud" text;
    "lv_schema" text;
    "lv_schema_ud" text;
    "lv_object" text;
    "lv_object_ud" text;
    "lv_replacement" text;
    "lv_object_separator" text;
BEGIN
    DROP TABLE IF EXISTS "t_snippet_3";
    CREATE TEMPORARY TABLE "t_snippet_3" ("info" text, "value" text); -- ON COMMIT DROP;
   
    "lv_command" := '$surround$';
    "lv_curselesc" := '$CURRENT(selection, escape_quotes)$';
 
    -- do some stuff here
                       
    -- store the result
    INSERT INTO "t_snippet_3" ( "info", "value" ) VALUES ( 'lv_replacement', "lv_replacement");
END
$^$^ LANGUAGE plpgsql;
$$
$$
    -- retrieve the result
    SELECT tx."value" FROM "t_snippet_3" AS tx WHERE 1 = 1 AND tx."info" = 'lv_replacement';
$$
$$
    -- cleanup
    DROP TABLE IF EXISTS "t_snippet_3";
$$


but that has a very nasty side effect of putting a line break in an undesired place, thus when selecting the characters a + b in the code below and selecting the surround with (...) in the prompt, instead of converting this:
Code:

x = a + b * c

into this:
Code:

x = (a + b) * c

it turns it into this:
Code:

x =
    (a + b)  * c

That's not good so I tried to move the $$...$$ macros close together by making them "touch" but while SA forgives me doing so for the $prompt...$ and the $$...$$ macros
Code:

$PROMPT(surround,Surround with what? :),,Surrounder, (...) [...] [#...])$$$
DO
$^$^
DECLARE
    "lv_command" text;
.
.
.

it is not so lenient when it comes to clashing two $$...$$ macros:
Code:

$PROMPT(surround,Surround with what? :),,Surrounder, (...) [...] [#...])$$$
DO
$^$^
DECLARE
    "lv_command" text;
.
.
.
    INSERT INTO "t_snippet_3" ( "info", "value" ) VALUES ( 'lv_replacement', "lv_replacement");
END
$^$^ LANGUAGE plpgsql;
$$$$
    -- retrieve the result
    SELECT tx."value" FROM "t_snippet_3" AS tx WHERE 1 = 1 AND tx."info" = 'lv_replacement';
$$


Putting a space between the two meeting pairs of $$ like this
Code:

.
.
.
$$ $$
   SELECT tx."value" FROM "t_snippet_3" AS tx WHERE 1 = 1 AND tx."info" = 'lv_replacement';
$$ $$
   DROP TABLE IF EXISTS "t_snippet_3";
$$

works but it still leaves me with two rather unpleasant surplus spaces:
Code:

x =  (a + b)  * c

While not having those spaces is not critical in this example but might cause problems in other cases.

Any hints on how to make this work? I can mangle the code to be a single SELECT statement but the result is remarkably ugly and a nightmare to maintain.

UPDATE: It also adds a new line at the end of the inserted/processed text that I found no way to get rid of, and that's an issue that affects the mangled version as well.
Fri Jan 14, 2022 8:33 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
IMHO, this is not a rocket science, but it gets quite complicated here. The sort of hairy syntax used here isn't for human code reading :-)
Would it be easier to create a function in PostgreSQL to execute dynamic queries and have the macro pass all or parts of what needs to be executed as a string parameter(s)? You wouldn't need to deal with nested $$/$^$^

Kind of
Code:
SELECT * FROM my_function(
   param1 => '$$ something macro driven here $$',
   param2 => 'something else'
);


As an added benefit, you wouldn't need to deal with temp tables, or even if you still need them, have Postgres automatically drop them on commit as soon as it completes the function
Fri Jan 14, 2022 11:14 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I don't mind nesting the $^$^ part or using temporary tables, that's a piece of cake. From my point of view, the problem is that I cannot achieve my goal in a simple SELECT. The main part of this problem is that in Postgres the $$...$$ macro works by creating a prepared statement and executing it and prepared statements are only capable of preparing one single statement. I can make it work in a single SELECT, but that isn't nice and is hard to fix/modify. I can do the stuff in an anonymous block nice and clean (one DO "statement"), but that dumb piece of brick cannot return result sets (why?). That's why I thought I would do it in two $$...$$ macros. Or rather three, because I cannot do the SELECT and DROP in one common $$...$$ either. Dropping the temporary table is not necessary, so I might reduce it back to two, but even that two will still have to "meet" somewhere and they create that space gap there.

This snippet would be used rather frequently (it does some transformations to the selected code part and then reintroduces it into the code), and I'd use it virtually every time and everywhere. Since I'm working with dozens of servers and many databases on each, it would require deploying that function to hundreds of databases. Not to mention, that I may or may not have the necessary permissions to deploy anything at all. Alas, that's a no-go for the function-based solution.

Currently, I'm back to the single SELECT solution because constantly having to fix the code disfiguration caused by those surplus spaces reduces the gain provided by the snippet to zero (or even makes it become a hindrance). Now, if you think this above is not for human eyes, you should not peek at the SELECT, it might cause permanent brain damage :D It surely does to me each time I have to add/remove something...
Sun Jan 16, 2022 9:27 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
UPDATE:
I nailed it with some nicely combined CTEs. They're are all as simple as it gets, it's a single SELECT, no anonymous blocks, no temporary tables, no multiple $$...$$ macros. Everyone is happy. Well, sort of. The only thing that still gets in the way is the surplus new line at the end of the reinserted code part. But that's still faster to solve than the spaces.
Sun Jan 16, 2022 10:00 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
Nice! Good to know you have found simple and efficient solution. Hopefully that surplus line isn't a big deal.
Sun Jan 16, 2022 11:06 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Actually, it is a big deal. But not because it's there, though that would be mildly annoying if that's how it was intended to work I would have to accept it. The reason it is a big deal is that it's there because of a bug.

I've created a snippet that is triggered on Alt+4. In its original form, it doesn't do much, it simply selects the selected string back into the editor. I took the liberty and opened the sqlassist.sas file in an editor where I can see the source of the snippet, including any non-visible/printable characters. Those small screenshots are in dark blue.

When I first saved the snippets, it was a single line on, consisting of this string in the .sas file:
Quote:

body=$$select '$CURRENT(selection)$';$$<0x03>

The characters in surrounded by <> are non-visible in the Options window. I observed that in every single-line snippet the body part is closed by a <0x03> and multiline snippets are ended by <0x02>. So far this snippet did what it was told to do: it put back the selected string into the editor.

Now, I added a new line and saved the snippet again. The source (the body part is the relevant again) changed into:
Quote:

body=$$select '$CURRENT(selection)$';$$<0x0e><0x0b><0x02>


Here, the body part is closed with <0x02> (so it is multiline, even if the second line is completely empty). The <0x0e><0x0b> is (I guess) <0x0d><0x0a> (CR and LF) shifted a bit higher so that they would not make an actual new line in the file. Selecting a string and hitting Alt+4 pasted the string and a new line. So far, so good, this is expected. Returning to the snippet editor, I realized that there was something strange. I wanted to remove the surplus line but it wasn't there. I experimented a bit and noticed that after applying the change, the snippet editor no longer showed that surplus line if it was empty. I couldn't move the cursor to the second line, therefore I couldn't remove it by pressing Backspace like I usually do. Heck, if this wasn't an experiment where I carefully planned each step I wouldn't even know where would be a new line. But checking the .sas file it was clear that the new line didn't go away, it was only hiding. I removed it from the .sas file in the text editor, saved it, and all returned to normal.

Check this short video here, where I demonstrate it.

By the way, the non-visible surplus can be removed by moving the caret to the last possible position and pressing the Delete key and now that I had removed the surplus line from my original snippet it no longer gives me the itch to bash a skull or two :) Still, it would be nicer if there was visible proof of its presence (that is, the caret could be moved to it).
Mon Jan 17, 2022 5:22 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
Quote:
The <0x0e><0x0b> is (I guess) <0x0d><0x0a> (CR and LF) shifted a bit higher so that they would not make an actual new line in the file


Correct. But why <0x03> turns to <0x02> isn't clear to me. It's been behaving like that for many years, I'm sure there is a reason for that, which I don't know yet. Let me get back to you on this in a day or two once I find out more details.
Mon Jan 17, 2022 2:31 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
Quote:
Still, it would be nicer if there was visible proof of its presence


To make them visible, in the context menu choose "Show Control Characters", this will make all non-printable characters visible and easier to delete.
Tue Jan 18, 2022 12:32 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Excellent idea.
Tue Jan 18, 2022 7:24 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
I found out the reason for that behavior. Long story short, without going into deep technical details, the extra line is added by the snippet deliberately to deal with a different type of issue, that's by design. We are aiming at fixing that in v12.1 a different way that won't require adding an extra line.
Thu Jan 20, 2022 1:17 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.