pl/pgsql "Run SQL" over agressive

[Indribell]

• OS: Debian
• Database Type: PostgreSQL

A plpgSQL function with the following content in the database:

DROP FUNCTION IF EXISTS fn_change_user_name;

CREATE OR REPLACE FUNCTION public.fn_change_user_name(p_user_id integer, p_name text)
RETURNS vw_user
LANGUAGE plpgsql
AS $function$
UPDATE public.user set u_firstname = p_name where id = p_user_id;
SELECT * FROM vw_user WHERE id = p_user_id; 
$function$

The plugin will show

**> Run SQL**
DROP FUNCTION IF EXISTS fn_change_user_name;

**> Run SQL**
CREATE OR REPLACE FUNCTION public.fn_change_user_name(p_user_id integer, p_name text)
RETURNS vw_user
LANGUAGE plpgsql
AS $function$
UPDATE public.user set u_firstname = p_name where id = p_user_id;
**> Run SQL**
SELECT * FROM vw_user WHERE id = p_user_id; 
**> Run SQL**
$function$

Because it seems to only check if a SQL command ends with dot comma. But pl/pgsql functions are inside function blocks that are indicated with $$ for start, and $$ for end. Or named $name$ for start and $name$ for end.

Expected result:

**> Run SQL**
DROP FUNCTION IF EXISTS fn_change_user_name;

**> Run SQL**
CREATE OR REPLACE FUNCTION public.fn_change_user_name(p_user_id integer, p_name text)
RETURNS vw_user
LANGUAGE plpgsql
AS $function$
UPDATE public.user set u_firstname = p_name where id = p_user_id;
SELECT * FROM vw_user WHERE id = p_user_id; 
$function$

In other words, SQL block recognition is missing.

[cweijan]

Due to the flexibility of the stored procedure syntax, these SQL cannot be correctly identified. If you need to write a stored procedure, it is recommended to check this option to disable codelen.

[Indribell]

Does not solve the issue:

Disable SQL Code len; True
Restart VSC...

Open Function from database:

> Run SQL
DROP FUNCTION IF EXISTS fn_change_user_name;

> Run SQL
CREATE OR REPLACE FUNCTION public.fn_change_user_name(p_user_id integer, p_name text)
 RETURNS vw_user
 LANGUAGE sql
AS $function$
UPDATE public.user set u_firstname = p_name where id = p_user_id;
> Run SQL
select * from vw_user where id = p_user_id; 
> Run SQL
$function$

Its still polluting the SQL commands with the "Run SQL".

[cweijan]

Hi, using the latest version 5.0.8.

[Indribell]

I was using the "PostgreSQL v5.0.3".

The Database Client v5.0.8 version does not work. The "Create Connection" results in a empty "Connect" page. Restarted VSC several times, no other DB extentions, logs are clear of any errors etc...

Ironically the Disable SQL Code len; True works, when loading a SQL file from disk. So the code len works but the Connection page is broken.

[cweijan]

If you have database client installed, you must uninstall PostgreSQL extension.

[Indribell]

If you have database client installed, you must uninstall PostgreSQL extension.

Did that before installing the Database Client...

Steps:

• Install PostgreSQL
• Install Database Client
• Remove PostgreSQL
• Restart

-- Not working Connect

• Already Installed Database Client
• Install PostgreSQL
• Remove PostgreSQL
• Restart

-- Not working Connect

• Remove Database Client (No Postgresql)
• Install Database Client (No Postgresql)
• Restart

-- Not working Connect

• Remove Database Client (No Postgresql)
• Restart
• Install PostgreSQL
• Restart
• Broken PostgreSQL, does not start
• Shut down WSL2
• Still broken PostgreSQL
• Remove PostgreSQL
• Reinstall PostgreSQL
• ... few times with VSC restarts
• Works again... with Connect for the PostgreSQL version working.
• Reinstall PostgreSQL
• Restart VSC
• Install Database Client
• Restart VSC

-- Not working Connect for Database Client

• Install Database Client (No Postgresql)
• Remove Database Client (No Postgresql)
• Install Database Client (No Postgresql)

Note: Console also does not work. History does work. Commands (No active database connection found) but technically works. It seems that the Console and Connect pages are simply bonkers.

That is a series of tests that i have run. The old PostgreSQL version has no issue with the connect and other pages. Beyond that one time that the install failed, it worked without issue. The Database client version 5.0.8 is simply broken.

For now i am giving up because its just wasting my time at this point.

[cweijan]

I don't think you need to do so many troublesome tests, I have released multiple extensions with the same function, you can't install them at the same time, otherwise it will cause a function conflict.

[cweijan]

The reason why disabling codelen in postgresql fails is because in version 5.0.3 this option is only work for premium users.

[Indribell]

I don't think you need to do so many troublesome tests, I have released multiple extensions with the same function, you can't install them at the same time, otherwise it will cause a function conflict.

The issue with the lacking connect and other pages has nothing to do with multiple extensions or single extensions.

The old 5.0.3 PostgreSQL extension calls the following:

Request URL: https://vscode-remote+wsl-002bdebian.vscode-resource.vscode-webview.net/home/benny/.vscode-server/extensions/cweijan.vscode-postgresql-client2-5.0.3/out/webview/js/vendor.js
App, Oldcomptable.js etc

Here is what the 5.0.8 Database Client extension calls:

http://127.0.0.1:8828//js/vendor.js
... the same for the rest like App, Oldcomptable.js etc

And of course it can not find anything as its trying to find something on the localhost that does not exist.

For some reason the extension is falling back on hardcoded local IP, where as VSC expects the plugin for WSL2 development with the https://vscode-remote+wsl-0.... path.

Not sure how or why this bug happens only with the newer 5.0.8 plugins and not with the 5.0.3 Postgresql. So something must have changed between the 5.0.3 and 5.0.8 codebase.

[cweijan]

Sorry I didn't know your environment is remote-wsl. If it is a normal environment or remote-ssh, it all works fine. I will try to support remote-wsl.

[benzdouglas]

Coincidentally having the exact same issue trying to create a stored procedure in MySQL where the ending semicolon in the procedure is causing the extension to interpret as two separate statements. I found a workaround by choosing "Run All SQL" instead of "Run Selected SQL" and it worked

Example code:
CREATE OR REPLACE PROCEDURE getTickets() BEGIN SELECT id FROM tickets; END;

[cweijan]

Since we don't use stored procedures in China, so I didn't plan to support stored procedures before, but it seems that many people need it, and I will enhance the support for stored procedures codelen in future versions.

[cweijan]

Hi, I rolled back the code in version 5.0.9, now not use the http server, and I will enhance the support for stored procedures in the future.

[Indribell]

Hi, I rolled back the code in version 5.0.9, now not use the http server,

Verified. 5.0.9 works without issue with the connect and other pages.

Since we don't use stored procedures in China

Its rarely used for common web development because people do not understand the benefits for them. And for lighter websites they can be considered overkill. Hell, people still ignore prepared statements.

You see it more in medium or big companies, even in China. The reality is, store functions separate your data logic from your application logic and can result in easily 100 a 1000% more performance as your saving in roundtrips to your (separate!) DB server ( including other benefits like reduced cpu and memory overhead ).

Anyway, seeing as the bug has been fixed for now and the other issue will be future fixed, i will close the ticket. Thanks you for the support cweijan.