Why running function created in with table data returned raised error ?

[sergeynilov]

In laravel9.6.0 app using tpetry/laravel-postgresql-enhanced 0.20.0 I create function with table data returned :

return new class extends Migration
{
    public function up()
    {
        Schema::createFunctionOrReplace('ts_categories_get', ['sort_field' => 'character varying'],
            'TABLE(id smallint, name character varying, active boolean, slug character varying, description text,
             created_at timestamp, updated_at timestamp)', 'plpgsql', '

  BEGIN
    SELECT ts_categories.id, ts_categories.name, ts_categories.active, ts_categories.slug, ts_categories.description,
    ts_categories.created_at, ts_categories.updated_at
        from ts_categories;
  END;

');
    }

But running the function : SELECT ts_categories_get('name') I got error : ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function ts_categories_get(character varying) line 4 at SQL statement In statement: SELECT ts_categories_get('name') I see in phppgadmin this function defined as : https://prnt.sc/TuNTxr6-TcxM

What is wrong ?

Thanks!

[tpetry]

Returning a table is not yet supported. I didn't expect it to be required that early. I've added it to my todo list.

[tpetry]

The migration has run successfully, which I did not expect. Can you share the manual SQL query and the one executed in the migration by using --pretend?

[sergeynilov]

I got :

$ php artisan migrate:fresh --seed  --pretend

                                          
  The "--pretend" option does not exist.  
                                          

Invalid key ?

[sergeynilov]

Manually I create function with script :

CREATE OR REPLACE FUNCTION ts_categories_get(sort_field character varying) RETURNS TABLE(id smallint, name character varying, active boolean, slug character varying, description text, created_at timestamp, updated_at timestamp)
    LANGUAGE sql
    AS $$

SELECT id, name, active, slug, description, created_at, updated_at

from ts_categories


ORDER BY sort_field

    $$;

[tpetry]

The migrate:fresh command does not support --pretend. Rollback all your changes and do a normal php artisan migrate --seed --pretend call.

[sergeynilov]

I got :

$ php artisan migrate --seed --pretend

   INFO  Preparing database.  

  Creating migration table ............................................................................................................... 63ms DONE

   INFO  Running migrations.  

  CreateMediaTable .................................................................................................................................  
  ⇂ create table "media" ("id" bigserial primary key not null, "model_type" varchar(255) not null, "model_id" bigint not null, "uuid" uuid null, "collection_name" varchar(255) not null, "name" varchar(255) not null, "file_name" varchar(255) not null, "mime_type" varchar(255) null, "disk" varchar(255) not null, "conversions_disk" varchar(255) null, "size" bigint not null, "manipulations" json not null, "custom_properties" json not null, "generated_conversions" json not null, "responsive_images" json not null, "order_column" integer null, "created_at" timestamp(0) without time zone null, "updated_at" timestamp(0) without time zone null)  
  ⇂ create index "media_model_type_model_id_index" on "media" ("model_type", "model_id")  
  ⇂ alter table "media" add constraint "media_uuid_unique" unique ("uuid")  
  ⇂ create index "media_order_column_index" on "media" ("order_column")  
  CreatePermissionTables ...........................................................................................................................  

   ErrorException 

  Undefined array key 0

  at vendor/laravel/framework/src/Illuminate/Database/Query/Processors/PostgresProcessor.php:24
     20▕         $connection = $query->getConnection();
     21▕ 
     22▕         $connection->recordsHaveBeenModified();
     23▕ 
  ➜  24▕         $result = $connection->selectFromWriteConnection($sql, $values)[0];
     25▕ 
     26▕         $sequence = $sequence ?: 'id';
     27▕ 
     28▕         $id = is_object($result) ? $result->{$sequence} : $result[$sequence];

      +13 vendor frames 
  14  database/seeders/RolesAndPermissionsSeeder.php:22
      Spatie\Permission\Models\Permission::create()

      +25 vendor frames 
  40  database/migrations/2014_10_11_154105_create_permission_tables.php:124
      Illuminate\Support\Facades\Facade::__callStatic()

[tpetry]

That's a bug for the seeder related to the spatie permission package, just execute without --seed. And please debug by yourself any further problems of your script, that is not my task.

[jaulz]

Also, in the first example you created the function with pgplsql and in the manual example you use SQL.

[tpetry]

As highlighted before by @jaulz had your function declarations been different. Also, creating functions that return a table was not implemented.

But with release 0.22.0 you can now create functions returning a table.