Better IDE integration?

[benjamin-thomas]

Hello, I'm testing out your library.

I'm wondering if you have any advice or opinion on better using IDEs with it.

Jetbrains IDEs have a wonderful SQL auto complete (from dynamically inspecting the schema).

However, the pggen namespace is unknown is so we get useless error messages.

Injecting a bogus function in the dev environment does the trick.

-- Improve IDE integration
CREATE SCHEMA pggen;
CREATE FUNCTION pggen.arg(str VARCHAR) RETURNS INTEGER AS
$$
BEGIN
    RETURN 1;
END;
$$ language plpgsql;

One can always clean this up if need be:

-- Remove IDE integration improvements
DROP FUNCTION pggen.arg(str VARCHAR);
DROP SCHEMA pggen;

I'm thinking this little hack could be mentioned in the README. Or maybe it'd be ok to let the tooling handle this, by adding a switch, something like pggen gen --add-ide-integration.

What do you think?

[jschaf]

Heh, I do the same thing. A mention in the readme feels about right.

-- This schema file exists solely so IntelliJ doesn't underline every
-- pggen.arg() expression in squiggly red.
CREATE SCHEMA pggen;

-- pggen.arg defines a named parameter that's eventually compiled into a
-- placeholder for a prepared query: $1, $2, etc.
CREATE FUNCTION pggen.arg(param text) RETURNS any AS
'';

[jschaf]

I should clarify, you don't need to have this actually in your database. You can drag the file over into the DB tab as a DDL schema and include in the SQL resolution scopes (which defaults to all databases so it should be good out of the box).

[benjamin-thomas]

Thanks @jschaf. Dragging a file as a DDL data source did not work for me.

That could be due to your function returning a syntax error for me though (I'm running on PostgreSQL 10.14).

I did not know about the ANY return type. There's also an ANYELEMENT type that looks like it could be useful but I need to research things further.

Injecting this bogus function into one's dev database remains useful though. That way, it's possible to run the queries and adapt them dynamically in the IDE.

Here's my latest idea.

-- Improve IDE integration
CREATE SCHEMA pggen;
CREATE FUNCTION pggen.arg(param VARCHAR) RETURNS INT AS
$$
BEGIN
    CASE param
        WHEN 'limit' THEN
            RAISE WARNING 'LIMIT=10';
            RETURN 10;
        WHEN 'offset' THEN RETURN 0;
        ELSE RETURN 1;
    END CASE;
END;
$$ language plpgsql;

-- Remove IDE integration improvements
DROP FUNCTION pggen.arg(param VARCHAR);
DROP SCHEMA pggen;

I inject extra info via the raise call, to get clarity from the database logs.

I'd like to hear you thoughts if you have any other ideas.

And have more info on how you use the ANY return type. Then I'll make a PR for a README update if you're ok with that.

psql shell output below:

dev_db=# CREATE FUNCTION pggen.arg(param text) RETURNS any AS
dev_db-# '';
ERROR:  syntax error at or near "any"
LINE 1: CREATE FUNCTION pggen.arg(param text) RETURNS any AS

[jschaf]

That could be due to your function returning a syntax error for me though (I'm running on PostgreSQL 10.14).

I think that stub function parses cleanly in IntelliJ but isn't runnable since it looks like the plpgsql and sql languages don't support the any type. It'd be nice to mark the function as generic, but if we want a return type of anyelement, we must also have at least one input type of anyelement but the only input parameter must be of type text. The most general we can probably make this function is something like:

CREATE FUNCTION pggen.arg(param text) RETURNS text AS
'SELECT NULL' LANGUAGE sql;

Injecting this bogus function into one's dev database remains useful though. That way, it's possible to run the queries and adapt them dynamically in the IDE.

I don't think this is possible in the general case since pggen.arg can be replaced with any possible type. As an example, to replace with a text array (the go code will use []string).

SELECT pggen.arg('some_array')::text[];

I think actually executing the pggen dummy code is too tricky to support in a reasonable way. The most I'm comfortable recommending is using the dummy code as a DDL schema for type completion in IntelliJ. I wouldn't recommend it for actually running since the it can change how a query executes.

[benjamin-thomas]

@jschaf let's keep it simple indeed :)

I've opened a PR if you want to have a look. Closing this for now.