Hi there I m actually trying to call a pg function via prism Prisma #orm-help

Hi there, I'm actually trying to call a pg functio...

Andrew Valleteau

03/08/2022, 9:45 AM

Hi there, I'm actually trying to call a pg function via prisma.$executeRaw and a have a little troubles finding the correct syntax. My function look like so:

Copy code

// IN POSTGRESQL
type permission_type (entity_id uuid, access text);
update_permissions(target_id uuid, new_permissions permission_type[])

// In Typescript
const entityId = 'abc-def-hij'
const newPerms = [{id: 'abc-def-hig', access: 'write'}];
await prisma.$executeRaw(`SELECT update_permission(${entityId}, array[${Prisma.join(newPerms.map((p) => `(${p.id}, ${p.access})`)}]::permission_type[]))`);

Would that be correct ?

Andrew Valleteau

03/08/2022, 10:30 AM

After severals attempts, doesn't seems like the proper way to do this query.

Andrew Valleteau

03/08/2022, 10:44 AM

Also tried this syntax, but it give me an "ERROR: wrong number of columns: 673670755, expected 2"

Andrew Valleteau

03/08/2022, 10:44 AM

Copy code

const preparedOrgsPerms = props.orgs.map(
    (perm) => `('${perm.id}', '${perm.access}')::permission_type`
  );
  const preparedTeamsPerms = props.teams.map(
    (perm) => `('${perm.id}, '${perm.access}')::permission_type`
  );
  const preparedUsersPerms = props.users.map(
    (perm) => `('${perm.id}', '${perm.access}')::permission_type`
  );
  await prisma.$executeRaw`
    SELECT update_folder_permissions(
      ${projectId},
      ${parentId},
      ${props.folderId},
      array[${Prisma.join(preparedOrgsPerms)}]::permission_type[],
      array[${Prisma.join(preparedTeamsPerms)}]::permission_type[],
      array[${Prisma.join(preparedUsersPerms)}]::permission_type[]
    );
  `;

janpio

03/09/2022, 5:43 PM

Please open an issue if you can not figure this out. Thanks.

Open in Slack

Previous Next