Unsupported feature 'assignment from non-constant source expression'

Question

We are getting the below error when executing a standard script:

Error: Unsupported feature 'assignment from non-constant source expression'.

Script set geo = (Select st_makepoint(-79.3810586,43.6562331));

Answer 1

So you SQL run by itself, that's a good start:

Select st_makepoint(-79.3810586,43.6562331);

ST_MAKEPOINT(-79.3810586,43.6562331)
{ "coordinates": [ -7.938105860000000e+01, 4.365623310000000e+01 ], "type": "Point" }

so a simple block with the dynamic type let:

begin
    let geo := (Select st_makepoint(-79.3810586,43.6562331));
    return geo;
end;

092228 (P0000): SQL compilation error: error line 2 at position 4

variable 'GEO' cannot have its type inferred from initializer

so declaring the type ahead of time give:

declare
    geo GEOGRAPHY;
begin
    geo := (Select st_makepoint(-79.3810586,43.6562331));
    return geo;
end;

000603 (XX000): SQL execution internal error:

Processing aborted due to error 300010:3443530546; incident 6816246.

that not good. but it might be related to the fact it's not in GA yet.

https://docs.snowflake.com/en/developer-guide/snowflake-scripting/variables.html#declaring-a-variable

The data type of the variable. This can be:

A SQL data type (except for GEOGRAPHY in this preview version).

so if you have the preview feature turned on the above might work for you..

But by "standard procedure" you mean a JavaScript Procedure:

create procedure f()
returns GEOGRAPHY
language javascript
as
$$
    var geo_sql = 'Select st_makepoint(-79.3810586,43.6562331)';
    
    var stmt1 = snowflake.createStatement( { sqlText: geo_sql } );

    var results1 = stmt1.execute();

    results1.next();
    
    return results1.getColumnValue(1);
$$
;

which we can call

call f();

F
{ "coordinates": [ -7.938105860000000e+01, 4.365623310000000e+01 ], "type": "Point" }

so that works..