Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Please support AWS Redshift #264

Closed
forrcaho opened this issue Jun 4, 2019 · 9 comments

Comments

2 participants
@forrcaho
Copy link

commented Jun 4, 2019

AWS Redshift is a dbms specializing in data warehousing originally forked from PostgreSQL 8.x.

I tried using the postgresql driver to connect, but get this error:

ERROR: Error:  function pg_get_function_result(oid) does not exist

This function was introduced with postgresql version 9.0.

A postgresql 8 compatible driver should be able to work with AWS Redshift.

@project-bot project-bot bot added this to To do in Kanban Jun 4, 2019

@mtxr

This comment has been minimized.

Copy link
Owner

commented Jun 4, 2019

Hi @forrcaho!

PostgreSQL 8.x doesn't have this function pg_get_function_result so Redshift.

Do you have any ideas of how to list functions (system/users) in Redshift? I don't have any idea now, but I'll take a look on how to make it work. Is there any other tool (DBeaver, datagrip) that provides function listing for Redshift that you are aware?

Thanks!

@forrcaho

This comment has been minimized.

Copy link
Author

commented Jun 4, 2019

In my own case, we don't use functions (or stored procedures, which I suspect will exhibit the same problem) in our Redshift instance. So, disabling any function/stored procedure querying would probably be the simplest fix, and that would most likely work fine for me personally.

That said, it appears that functions and stored procedures do exist in Redshift, as documented from a database user perspective here:
https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_FUNCTION.html
https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_PROCEDURE.html

I used Postrgres a bit in the 8.x days, and while I'm no expert, I think that the way to get all the function/procedure information you may need would be to query the pg_proc system catalog:
https://www.postgresql.org/docs/8.0/catalog-pg-proc.html

@mtxr

This comment has been minimized.

Copy link
Owner

commented Jun 4, 2019

What's the output of this query:

SELECT
  n.nspname AS schema,
  f.proname AS name,
  current_database() AS database,
  quote_ident(n.nspname) || '.' || quote_ident(f.proname) AS signature,
  format_type(f.prorettype, null) AS "resultType",
  oidvectortypes(f.proargtypes) AS args,
  current_database() || '/' || n.nspname || '/' || 'procedures' || '/' || f.proname AS tree,
  f.prosrc AS source
FROM
  pg_catalog.pg_proc AS f
  INNER JOIN pg_catalog.pg_namespace AS n on n.oid = f.pronamespace
WHERE
  n.nspname not in ('pg_catalog', 'information_schema')
;

If it works, we can still list functions and procedures for both postgres and redshift. The plan is to use this listing later on intellisense, so I prefer to keep it for all dialect.

Let me know if this query works.

Thanks again @forrcaho !

@forrcaho

This comment has been minimized.

Copy link
Author

commented Jun 4, 2019

The query works! I can't share the results as it's a private database, but there were some rows returned, and it looks fine.

@mtxr

This comment has been minimized.

Copy link
Owner

commented Jun 4, 2019

Nice! Would you like to contribute to the project? We basically need to replace this query on

fetchFunctions: `
SELECT
n.nspname AS schema,
f.proname AS name,
current_database() AS database,
quote_ident(n.nspname) || '.' || quote_ident(f.proname) AS signature,
pg_get_function_result(f.oid) AS "resultType",
pg_get_function_arguments(f.oid) AS args,
current_database() || '${TREE_SEP}' || n.nspname || '${TREE_SEP}' || 'procedures' || '${TREE_SEP}' || f.proname AS tree,
f.prosrc AS source
FROM
pg_catalog.pg_proc AS f
INNER JOIN pg_catalog.pg_namespace AS n on n.oid = f.pronamespace
WHERE
n.nspname not in ('pg_catalog', 'information_schema')
ORDER BY name
;`,

After replacing, you can easily build and test it there, check the explorer on sidebar etc..

If it's ok for you, I would appreciate. I don't have an easy way to test it on Redshift right now, but that query works for postgres as well cause I've tested it here.

Let me know if you can do this, otherwise I'll need to wait till the weekend to fix and test that.

Thanks!

@forrcaho

This comment has been minimized.

Copy link
Author

commented Jun 4, 2019

I don't think I'd be able to get to it any sooner than you. My situation is complicated a little by the fact that I'm using VSCode Insiders Remote - SSH and don't have access to the db from my local system.

I looked in the ~/.vscode-server-insiders/extensions/mtxr.sqltools-0.19.4 directory and it appears that the Typescript is all precompiled by the time it's stored there, so it's not just swapping out a file.

Hopefully (when you have time, of course) you'll be able to make a packaged extension I'll be able to install.

@mtxr mtxr closed this in 8864279 Jun 7, 2019

Kanban automation moved this from To do to To be released Jun 7, 2019

@mtxr

This comment has been minimized.

Copy link
Owner

commented Jun 7, 2019

Hey @forrcaho!

I've just published a new version that supports Redshift. Please test it and let me know how it goes. Hope everything works perfectly!

Also, if you like the project, please consider a donation or reviewing it on VSCode marketplace. That you help it to keep evolving.

Thanks!

@forrcaho

This comment has been minimized.

Copy link
Author

commented Jun 9, 2019

Confirmed that this works for me. Thanks!

@mtxr

This comment has been minimized.

Copy link
Owner

commented Jun 10, 2019

HI @forrcaho !

Glad to hear that!

Just came to remember, if you like the project, please consider a donation or reviewing it on VSCode marketplace. That you help it to keep evolving.

Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.