extend support for custom SQL functions (mutations, other functions types)

[dsandip]

1. Support for SQL functions in mutations
2. Support other function types

[revskill10]

@dsandip Is it possible to support pg notify/listen inside custom mutation, so that we could have custom subscription via pg notify/listen ?
The use case, is when we want to listen for a custom topic, like users:created, users:deleted,... which we could listen via a custom subscription.

subscription(topic:"users:created")

[sastraxi]

Would this include exposing volatile functions as mutations? The work-around I've found so far is to have a work log per function I want to call and simply use the event system's INSERT trigger to webhook out to a process which will carry out the desired mutation.

Apologies if I misunderstood this issue (in which case I'll open a new one).

[MhdSyrwan]

@shahidhk any updates on this (since I saw that you were planning to implement it)?

@revskill10 We'll add them in the coming iterations.

Originally posted by @shahidhk in #1073 (comment)

Thanks in advance.

[shahidhk]

@MhdSyrwan There is no exact ETA at the moment.

[tafelito]

Is there anyway to track the function that are manually created right now? Selecting the track option won't work as it says the function does not return a "COMPOSITE" type and if I refresh the browser I lose all the declarations

[marionschleifer]

@tafelito you can't return a composite type from a function. A function has to return a SETOF table.

[tafelito]

@marionschleifer so if I have something like this

-- Create the function
CREATE OR REPLACE FUNCTION insert_license()
  RETURNS trigger AS $BODY$
DECLARE licenses_avail INTEGER;
BEGIN
 SELECT a.licenses_avail INTO licenses_avail FROM "clients_codes" a WHERE a.id = NEW."client_code";
 IF licenses_avail <= 0 THEN
     RAISE EXCEPTION 'No more licenses available';
 END IF;
 RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

--- Create the trigger with the above function
CREATE TRIGGER insert_license BEFORE INSERT ON "licenses" FOR EACH ROW EXECUTE PROCEDURE insert_license();

I don't have a SETOF table to return. Is there any other way I can keep track of this functions?

[shahidhk]

@tafelito This is tracked in the Postgres schema as a trigger and the associated function. You don't want Hasura to "track" this.

Hasura's tracking is for functions which you want to expose as GraphQL queries.

[tafelito]

So if I need to see the functionsI created or if I want to edit one I have to connect directly to Postgress outside Hasura? Is that what you're saying?

Btw, I don;t want to expose this functions as GQL queries, but it'd be nice if I can edit them if I need them inside the hasura editor

[shahidhk]

Yes, since it has nothing to do with Hasura or GraphQL. You can use the Run SQL window in Hasura also. Just don't tick the "Track this" box.

[mnesarco]

Hi Friends, I am not sure if this is offtopic, but I managed to call PLPgsql Functions as mutations with Hasura 1.0 and I want to share my approach with you:

https://mnesarco.github.io/blog/2020/02/25/hasura-postgresql-function-as-mutation

[MhdSyrwan]

@mnesarco your approach is great and self-integrated. it allows developers even to inspect and trace previous calls and their responses! 👍

Question: I didn't get this part INSERT INTO action('sum'); how does it work without writing "VALUES" before ('sum')?
http://sqlfiddle.com/#!17/11e102/3

BTW, for people who want a simple quick solution, they may just create a view with a trigger to replace the create action of it in order to make a single callable function.

[mnesarco]

Hi @MhdSyrwan thanks for your review. I have fixed the missing "values" part.
I do not understand the view with trigger idea, can you explain it a little?

[MhdSyrwan]

Example:

You can create a dummy view like

CREATE VIEW my_action AS SELECT 'dummy';

Then write a trigger to implement its INSERT action like this:

CREATE OR REPLACE FUNCTION my_action_replace_insert ()
  RETURNS TRIGGER
  LANGUAGE plpgsql
  AS $function$
DECLARE
BEGIN
  -- function body here
END;
$function$;

CREATE TRIGGER my_action_replace_insert_trigger
    INSTEAD OF INSERT ON "my_action"
    FOR EACH ROW
    EXECUTE PROCEDURE my_action_replace_insert ();

Then, by tracking the my_action view in Hasura UI, you can call the function by calling the insert_my_action mutation.

[mnesarco]

I think that the view approach does not allow to return any result from the function but I can be wrong. I will try your idea and if it works i will add a section at the end of the article.

[MhdSyrwan]

You're right. it does not allow. Actually I didn't care about this feature since the whole point of this issue was to make a custom change in the database rather than to return something.

Functions that return value without altering DB are already supported check #1073.

Still, your approach would be the best for somebody trying to make a change and get some info at the same time.

[mnesarco]

Yes my solution is for calling functions without write restrictions, without input restrictions and without returning restrictions.

[mnesarco]

I have published a reference source code here: https://github.com/mnesarco/hasura-pg-actions
It could be useful for someone.

[RodolfoSilva]

@mnesarco. I think when actions is arrived we will have this feature built in. Right now we can do this with external functions. But you approach is very useful, but we need some permission checks before expose this for the users. I believe this is the way.

[karibertils]

I have a case where using a VOLATILE SQL function in a computed field would simplify things alot.

I also noticed I can create the function as STABLE, add it as computed field, and then change the function to VOLATILE and it works just fine in hasura.

But feels hacky and hasura complains on metadata reload. Also seem's there's not really much needed to support this except allowing VOLATILE function to be tracked, at least in my case.

Will the work for this issue support this use case as well ?

[Bessonov]

@karibertils look at linked draft pull request.

[karibertils]

@Bessonov I can see in the pull request that VOLATILE functions will be supported as mutations.

But I can't really tell if adding those mutations as computed fields will also be supported like with the STABLE functions ?

It might be a bit odd behaviour doing query { table { computed } } that's actually mutating data before returning the results.

[jberryman]

@karibertils There is no plan at the moment to allow VOLATILE functions to exist under the Query root field; the thinking is that because the graphql spec requires fields (except top-level mutation fields) to be idempotent and side-effect free, allowing a VOLATILE function would result in a graphql API that violates the spec (and e.g. causes issues with caching etc)

Can you give me an idea of your use-case? Maybe you have a function that is "morally" side-effect -free, but needs to be marked VOLATILE for some reason?

Also fwiw we also have some early plans to simplify/unify "function tracking" and "computed fields" APIs which should at least make this easier to talk about

[ayuryshev]

@jberryman Ability to query VOLATILE functions would open possiblity to implement server-side memoization.
Where only side-effect is cached calculation.
IDK is it breaks graphql specification but that would be great in practice.

It was several times when I started to think "aha ... and we can cache result so it .. Stop - hasura will no allow it.. Sad"

[jberryman]

@ayuryshev thanks! Yep the caching use-case you describe makes sense to me. I don't think that will happen in the VOLATILE function-as-mutations work, since it requires some discussion. Would you mind opening a feature request issue and mention the use-case you just outlined? It would also be helpful if you wanted to link to resources that describe the technique you're using.

[jberryman]

PR is in review ^

[revskill10]

Is this released in 1.4 ?

[hthillman]

Any update on when this will be released?

[ayuryshev]

@ayuryshev thanks! Yep the caching use-case you describe makes sense to me. I don't think that will happen in the VOLATILE function-as-mutations work, since it requires some discussion. Would you mind opening a feature request issue and mention the use-case you just outlined? It would also be helpful if you wanted to link to resources that describe the technique you're using.

@jberryman
Please take a look on: #6333 (comment)

[karibertils]

@jberryman

My use case for VOLATILE SQL function in query is as follows.

For every user there should always be exactly one in-progress order. If I query user(id: X) { current_order { ...order_details } } it should return that order.

If the user has no orders, or has only completed orders, it would create new empty order and return that as the current_order. If the user has existing in-progress order it would return the existing one. The intention is to simplify the API usage. Skipping the need to query, mutate, query again in cases with no current_order.

The orders can get finished in a few different ways, and feels less error-prone handling it at a single place rather than tracking it in many. I know this can be accomplished using SQL triggers or in the backend etc. But having a VOLATILE SQL function + computed field handle this felt like clean solid way.

[jberryman]

VOLATILE functions are supported as queries on master (see docs and linked PR). Apologies for not updating the discussion here, and thanks for the input!

[lancedouglas1]

Well done team Hasura.

Volatile functions are more valuable to the users of your product than the dogma of graphql abstaining from them.

I see this as an inflection point in customer value over technocracy that will serve Hasura well into being a global enterprise suite.